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"

Stichter

You will need some kind of auxiliary date table to get the months with no sales. – Tragic

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 – Stichter

why are you using different date formats in SELECT and GROUP BY? – Realpolitik

in select that is what i needed and in group by because to keep them sorted – Stichter

@Wasim to keep them sorted you need to use ORDER BY, grouping will work on Jun and Aug too – Realpolitik

order by will sort them alphabatically, So Aug will come at start – Stichter

@Wasim of course I meant ordering by date, not formated string. Anyway, sorry, I can't reproduce your problem: sqlfiddle – Realpolitik

@Tragic how to, please explain ... – Stichter

Answer 1

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

Stichter

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. – Gordie

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. – Stichter

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. – Gordie

@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 – Stichter

Hi, is there any way also get year with month AS 'Jan', like if i select date range between 2016 to 2018 – Zelmazelten

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. – Pleurodynia

Answer 2

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

Gravante

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 – Stichter

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.. – Stichter

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. – Gravante

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... – Pskov

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 – Pskov

Answer 3

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")

Brei