MySQL monthly Sale of last 12 months including months with no Sale

Question

SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as total
FROM cart
WHERE date <= NOW()
and date >= Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(date, "%m-%Y")

This query displaying result for only existing month. I need all 12 months sales.

Output:

"month" "total"  
--------------  
"Jun"   "22"
"Aug"   "30"
"Oct"   "19"
"Nov"   "123"
"Dec"   "410"

Required Output:

"month" "total"  
--------------
"Jan"   "0"
"Feb"   "0"
"Mar"   "0"
"Apr"   "0"
"May"   "0"
"Jun"   "22"
"Jul"   "0"
"Aug"   "30"
"Sep"   "0"
"Oct"   "19"
"Nov"   "123"
"Dec"   "410"

You will need some kind of auxiliary date table to get the months with no sales. — davek, Dec 22 '14 at 10:38
we can do that via SELECT 'Jan' AS MONTH UNION SELECT 'Feb' AS MONTH UNION SELECT 'Mar' AS MONTH UNION SELECT 'Apr' AS MONTH UNION SELECT 'May' AS MONTH UNION SELECT 'Jun' AS MONTH UNION SELECT 'Jul' AS MONTH UNION SELECT 'Aug' AS MONTH UNION SELECT 'Sep' AS MONTH UNION SELECT 'Oct' AS MONTH UNION SELECT 'Nov' AS MONTH UNION SELECT 'Dec' AS MONTH — Wasim A., Dec 22 '14 at 10:39
why are you using different date formats in `SELECT` and `GROUP BY`? — vladkras, Dec 22 '14 at 11:00
in select that is what i needed and in group by because to keep them sorted — Wasim A., Dec 22 '14 at 11:03
@Wasim to keep them sorted you need to use `ORDER BY`, grouping will work on `Jun` and `Aug` too — vladkras, Dec 22 '14 at 11:11
order by will sort them alphabatically, So Aug will come at start — Wasim A., Dec 22 '14 at 11:26
@Wasim of course I meant ordering by date, not formated string. Anyway, sorry, I can't reproduce your problem: [sqlfiddle](http://sqlfiddle.com/#!8/f64d7/5/0) — vladkras, Dec 22 '14 at 13:42

score 14 · Answer 1 · edited May 23 '17 at 12:01

Consider the following table

mysql> select * from cart ;
+------+------------+-------------+
| id   | date       | total_price |
+------+------------+-------------+
|    1 | 2014-01-01 |          10 |
|    2 | 2014-01-20 |          20 |
|    3 | 2014-02-03 |          30 |
|    4 | 2014-02-28 |          40 |
|    5 | 2014-06-01 |          50 |
|    6 | 2014-06-13 |          24 |
|    7 | 2014-12-12 |          45 |
|    8 | 2014-12-18 |          10 |
+------+------------+-------------+

Now as per the logic you are looking back one year and december will appear twice in the result i.e. dec 2013 and dec 2014 and if we need to have a separate count for them then we can use the following technique of generating dynamic date range MySql Single Table, Select last 7 days and include empty rows

t1.month,
t1.md,
coalesce(SUM(t1.amount+t2.amount), 0) AS total
from
(
  select DATE_FORMAT(a.Date,"%b") as month,
  DATE_FORMAT(a.Date, "%m-%Y") as md,
  '0' as  amount
  from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
  ) a
  where a.Date <= NOW() and a.Date >= Date_add(Now(),interval - 12 month)
  group by md
)t1
left join
(
  SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as amount ,DATE_FORMAT(date, "%m-%Y") as md
  FROM cart
  where Date <= NOW() and Date >= Date_add(Now(),interval - 12 month)
  GROUP BY md
)t2
on t2.md = t1.md 
group by t1.md
order by t1.md
;

Output will be

+-------+---------+-------+
| month | md      | total |
+-------+---------+-------+
| Jan   | 01-2014 |    30 |
| Feb   | 02-2014 |    70 |
| Mar   | 03-2014 |     0 |
| Apr   | 04-2014 |     0 |
| May   | 05-2014 |     0 |
| Jun   | 06-2014 |    74 |
| Jul   | 07-2014 |     0 |
| Aug   | 08-2014 |     0 |
| Sep   | 09-2014 |     0 |
| Oct   | 10-2014 |     0 |
| Nov   | 11-2014 |     0 |
| Dec   | 12-2013 |     0 |
| Dec   | 12-2014 |    55 |
+-------+---------+-------+
13 rows in set (0.00 sec)

And if you do not care about the above case i.e. dec 2014 and dec 2013

Then just change the group by in dynamic date part as

where a.Date <= NOW() and a.Date >= Date_add(Now(),interval - 12 month)
  group by month

and final group by as group by t1.month

Can you please explain the part where you did cross join in sub query. I want to know what you did here select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date — Wasim A., Dec 23 '14 at 03:42
another question, how you can make this query order by last 12 months, i tried by t2.date but incorrect sorting because of NULL created_on for not existing month.. — Wasim A., Dec 23 '14 at 03:48
`select curdate() - INTERVAL (a.a + (10 * b.a)` is generating the dynamic dates depending on the range you are looking at. Then this will do a left join with the origial query so all missing dates will appear from left table. Also for ordering use t1 data since t2 data may not be there always. — Abhik Chakraborty, Dec 23 '14 at 07:10
like this solution :) I just added `ORDER BY y.year ASC, x.month ASC` to have result for each months of 2015 first and 2016 after... — Meloman, Aug 17 '16 at 11:29
To have current year and last year dynamically, just replace the SELECT line as this : `SELECT YEAR(NOW()) -1 AS \`year\` UNION ALL SELECT YEAR(NOW())) AS y` — Meloman, Aug 17 '16 at 11:38

score 13 · Accepted Answer · answered Dec 22 '14 at 11:37

13

Thanks for @pankaj hint, Here i resolved it via this query...

SELECT 
    SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
    SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
    SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
    SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
    SUM(IF(month = 'May', total, 0)) AS 'May',
    SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
    SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
    SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
    SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
    SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
    SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
    SUM(IF(month = 'Dec', total, 0)) AS 'Dec',
    SUM(total) AS total_yearly
    FROM (
SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as total
FROM cart
WHERE date <= NOW() and date >= Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(date, "%m-%Y")) as sub

answered Dec 22 '14 at 11:37

Wasim A.

9,660
22
90
120

Yet, this is a *different* answer from your question. There you selected a columnar output, and correctly as per your comment determined you needed an auxiliary table. Here, you are generating a tuple output, twelve months every row. – LSerni Dec 22 '14 at 11:46
yes you are correct, i don't know how to do that with auxiliary table. Can you please explain. My solution is good to acceptable. – Wasim A. Dec 22 '14 at 17:05
I beg your pardon? Of course you know. *You* described how to do that in your comment... the `SELECT ... UNION` trick. Use that in a subselect, and presto, there's your auxiliary table. At first glance it also seems to me that @AbhikChakraborty posted the complete solution. – LSerni Dec 22 '14 at 17:52
@lserni i did it but its only picking Jan, not all other month names and i need month sorted like nov2014, dec2014, jan2015, feb2015 by date. This problem is really eating up my mind, 2 days has been passed – Wasim A. Dec 23 '14 at 02:53
Hi, is there any way also get year with month AS 'Jan', like if i select date range between 2016 to 2018 – Sanjay Kumar Sep 13 '18 at 05:34
Its not giving correct answer for the current month. Can you please check? I am using `MAX(total_price)` to get the maximum value of the month. but its returning smallest value of the previous month as a current month's value. – arslion Apr 08 '20 at 20:05

score 0 · Answer 3 · edited Jun 25 '21 at 07:55

0

Month wise sale Use Count to count month wise data.

SELECT DATE_FORMAT(date, "%b") AS month, COUNT(total_price) as total
FROM cart
WHERE date <= NOW()
and date >= Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(date, "%m-%Y")

edited Jun 25 '21 at 07:55

Slava Rozhnev

9,510
6
23
39

answered Jun 25 '21 at 05:39

Vipin Gangawane

1
1

MySQL monthly Sale of last 12 months including months with no Sale

3 Answers3

Linked

Related