Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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"
like image 531
Wasim A. Avatar asked Dec 22 '14 10:12

Wasim A.


1 Answers

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
like image 149
Wasim A. Avatar answered Sep 20 '22 20:09

Wasim A.