Reports month to month comparison chart over two years

Question

i'm working on a small PHP and Mysql project and i want o show my sales of the two last years for example 2018 and 2019. how can i show all the months of the year This is my sql query :

SELECT YEAR(b.creation_date) AS annee
 , MONTH(b.creation_date) AS mois
, SUM(price * quantity) AS total_ca 
  FROM invoicesitems a
  JOIN invoices b
 WHERE a.invoice_id = b.invoice_id 
    AND b.client = 6
GROUP BY YEAR(b.creation_date), MONTH(b.creation_date)
ORDER BY YEAR(b.creation_date), MONTH(b.creation_date)

And this is my result in English :

--------------------------
year | month | total_ca
--------------------------
2018 | 6     | 1548.00
--------------------------
2019 | 6     | 143.000

as you can see i have only month number 6 (june) how can i show all the months from 1 to 12.

Possible duplicate of [MySQL - count by month (including missing records)](https://stackoverflow.com/questions/34557199/mysql-count-by-month-including-missing-records) — Progman, Jun 29 '19 at 22:30

score 0 · Accepted Answer · edited Mar 13 '21 at 12:55

0

Be sure you have values for invoices in others months and years.

Also, you have a mix of explicit and implicit join syntax; try using a proper explicit ON clause:

  SELECT YEAR(invoices.creation_date) AS annee
    , MONTH(invoices.creation_date) AS mois
    , SUM(price * quantity) AS total_ca 
  FROM invoicesitems 
  INNER JOIN invoices  ON  invoicesitems.invoice_id = invoices.invoice_id 
  WHERE invoices.client = 6
  GROUP BY YEAR(invoices.creation_date), MONTH(invoices.creation_date)
  ORDER BY YEAR(invoices.creation_date), MONTH(invoices.creation_date)

edited Mar 13 '21 at 12:55

halfer

19,824
17
99
186

answered Jun 29 '19 at 19:25

ScaisEdge

131,976
10
91
107

thanks for your answer first, but i'm still getting only month number 6 ( june ) – Jun 29 '19 at 20:48
then based on your actual result .. update your question add a proper data sample .. and you expected . result – ScaisEdge Jun 30 '19 at 06:06

Reports month to month comparison chart over two years

1 Answers1