CODEWITHSUNDEEP
mysql
Callum
Callum

Reputation: 1165

MySQL group date fill in missing dates

I want to get data for each month in the year using group by month. Issue is that if one month has no data then it skips the month.

Is this something I can do within MySQL or should I try and figure out a solution in PHP? A MySQL solution would be the best I think.

Here's the query i'm going with:

SELECT 
  MONTH(date) as g, 
  SUM(gross) AS revenue, 
  ABS(SUM(fee)) AS fee, COUNT(*) AS volume
FROM transactions
WHERE
  date BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW() and
  gross > 0 and
  currency = 'USD'
GROUP BY g;

Upvotes: 0

Views: 266

Answers (2)

Elloco
Elloco

Reputation: 235

In one of my projects, I make this query in order to get the number of 'navettes' by 'Mois' (month in french) on the year '@annee' that is my parameter (C#). You may be inspired...

select 
 max(nbNavettes) as nbNavettes,
 min(Mois) as Mois 
 from (
    select 
     count(*) as nbNavettes,
     DATE (concat(year(s.dateLiv), '-', month(s.dateLiv), '-', '01')) AS Mois 
     from (
        select 
         min(num_remorques.dateLiv) as dateLiv 
         from num_remorques
         group by 
          concat(num_remorques.dateLiv,'::',num_remorques.numRemorque)
     ) s
     where 
        year(s.dateLiv)=@annee
     group by 
        year(s.dateLiv),
        Month(s.dateLiv)

    union
     select 0 as nbNavettes,
     concat(@annee,'-01-01') as Mois
    union
     select 0 as nbNavettes,
     concat(@annee,'-02-01') as Mois
    union
     select 0 as nbNavettes,
     concat(@annee,'-03-01') as Mois
    union
     select 0 as nbNavettes,
     concat(@annee,'-04-01') as Mois
    union
     select 0 as nbNavettes,
     concat(@annee,'-05-01') as Mois
    union
     select 0 as nbNavettes,
     concat(@annee,'-06-01') as Mois
    union
     select 0 as nbNavettes,
     concat(@annee,'-07-01') as Mois
    union
     select 0 as nbNavettes,
     concat(@annee,'-08-01') as Mois
    union
     select 0 as nbNavettes,
     concat(@annee,'-09-01') as Mois
    union
     select 0 as nbNavettes,
     concat(@annee,'-10-01') as Mois
    union
     select 0 as nbNavettes,
     concat(@annee,'-11-01') as Mois
    union
     select 0 as nbNavettes,
     concat(@annee,'-12-01') as Mois

 ) s2
group by 
 s2.mois

Note that I get monthes as Date format (yy-mm-01) and not simple int format. For your usage, you should replace

DATE (concat(year(s.dateLiv), '-', month(s.dateLiv), '-', '01')) AS Mois

by something like

month(s.dateLiv) as Mois

and

concat(@annee,'-04-01') as Mois

(and all same lines) by

4 as Mois

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If you have at least one row for each month (even one that doesn't match), then you can solve this using conditional aggregation:

SELECT MONTH(date) as g, 
       SUM(CASE WHEN gross > 0 and currency = 'USD' THEN gross ELSE 0 END) AS revenue, 
       ABS(SUM(CASE WHEN gross > 0 and currency = 'USD' THEN fee ELSE 0 END)) AS fee,
       SUM(gross > 0 and currency = 'USD') AS volume
FROM transactions
WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW() and
GROUP BY g;

This is not guaranteed to work. But sometimes this is a simple fix for this type of problem.

If not, then you would need to use a LEFT JOIN on a table that has all the months you want. It might be simpler to do the work in PHP.

Upvotes: 0

Related Questions