CODEWITHSUNDEEP
mysqlsqlmysql-8.0
Amelya
Amelya

Reputation: 43

GROUP BY for each Month and return 0 or NULL if not month exists (MySQL)

SELECT COALESCE(SUM(p.hours),0) as recap
FROM pointage p
WHERE YEAR(date_point) = '2020' AND p.user_id = 1
GROUP BY MONTH(date_point)

I would like sum hours realised for each month but actually in March others month does noot exist because is not present I would like obtain result like (only sum column but for it's an example)

Jan : NULL
Feb : 10
March : 42.75
APR : NULL
MAY : NULL
..
DEC : NULL

AND not only

Feb : 10
March : 42.75

Do you have a solution please?

Upvotes: 1

Views: 1060

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If you have data for all months in the table -- but just not for that user -- then the simplest (although not most efficient) approach is conditional aggregation:

SELECT MONTH(date_point) as mon,
       SUM(CASE WHEN p.user_id = 1 THEN p.hours END) as recap
FROM pointage p
WHERE YEAR(date_point) = 2020 
GROUP BY MONTH(date_point) ;

Otherwise, you need a list of the months, which can be generated in many ways -- a calendar table, recursive CTE, explicitly:

select m.mon, sum(p.hours)
from (select 'Jan' as mon, 1 as mm union all
      select 'Feb' as mon, 2 as mm union all
      . . .
     ) m left join
     pointage p
     on p.date_point >= '2020-01-01' and '2021-01-01' and
        p.user_id = 1 and
        month(p.date_point) = m.mm
group by m.mon
order by min(m.mm);

Upvotes: 1

Related Questions