Ahmed Emad Ahmed Emad - 4 months ago 13
SQL Question

Oracle : How to get the sum & count based on minimum date value

I have table as following :

Table 1 ( Column1,column2,column3,date)

I want to get count(column 1) & sum(column2) and the same count / sum when date is greater than the minimum date value in date column.

Although the 4 required value can be controlled by one condition on column 3.

Column 1 , Column 2 , Column 3 , Date
A 1 1 5/5/2016
G 5 0 5/10/2016
B 1 2 5/10/2016
A 12 1 5/10/2016
D 1 1 5/5/2016
A 1 1 5/11/2016
C 7 1 5/5/2016
C 1 1 5/12/2016
E 10 2 5/10/2016


I want when filter on column 3 = 1 get the following result :

Count (1) , Sum(2) , Count(1) when date greater than minimum ,Count(2) when date greater than minimum
3 , 23 , 2 , 14


I tried to use case put I don't need to group by values.

how can I generate query fulfill above requirements in oracle

Answer

You can use case. As you describe:

select count(column1), sum(column2),
       count(case when date > mindate then column1 end),
       sum(case when date > mindate then column2 end)
from (select t.*, min(date) over () as mindate
      from table1 t
     ) t;

This uses a window function to get the minimum date, which is used in the case.

Comments