CODEWITHSUNDEEP
sqlcountgroup-bysum
Magnolia
Magnolia

Reputation: 39

SQL : Count , Sum and Group by

I'm querying database :

ID    Date1       Date2        Status  FK
1    12-05-2016  20-05-2016    ok       3
2    24-05-2016  27-05-2016    NOK      3
3    29-05-2016  06-06-2016    OK       4

I need to get the sum grouping by month of date(date1 or date2) and fk.

Something like this :

Month        Sum        FK
5            2          3
5            1          4
6            1          4

I tried this, but it not giving me what I expected

select Month(date1)as date1 ,MONTH(date2) as date2 ,COUNT(*) as sum, fk
from table
where status ='OK'
group by fk , MONTH(date1), MONTH(date2);

Thanks in advance !

Upvotes: 1

Views: 91

Answers (1)

SSD
SSD

Reputation: 369

Try This:

select sum(sm),fk,dt from 
(
select Month(date1)as dt ,COUNT(*) as sm, fk

from table

where status ='OK'

group by fk , MONTH(date1)

union all
select Month(date2)as dt ,COUNT(*) as sm, fk

from table

where status ='OK'

group by fk , MONTH(date2)

)
group by dt,fk

Upvotes: 1

Related Questions