CODEWITHSUNDEEP
mysql
bashleigh
bashleigh

Reputation: 9314

Mysql group by month with null values if 0 entities

Really simple (can't find an example on here). Basically I want to add in the null values for missing months.

Currently I have 

|month| total |
----------------
   2  | 2454.34
   3  | 1254.34

Where as I want 

|month| total |
----------------
   1  |    0
   2  | 2454.34
   3  | 1254.34
   4  |    0
   5  |    0
   6  |    0

And so on.

My query so far 

SELECT MONTH(bookings.booking) as month, SUM(bookings.tendered) as total
FROM bookings 
INNER JOIN salons ON salons.id = bookings.salon_id
WHERE bookings.paid = 1
AND YEAR(bookings.booking) = 2017
GROUP BY MONTH(bookings.booking)
ORDER BY MONTH(bookings.booking);

I did try the following but doesn't seem to work the way I want it to? Excuse the different formats from above example 

SUM(IF(MONTH(bookings.booking) = 'Jan', bookings.tendered, 0)) AS 'Jan',
        SUM(IF(MONTH(bookings.booking) = 'Feb', bookings.tendered, 0)) AS 'Feb',
        SUM(IF(MONTH(bookings.booking) = 'Mar', bookings.tendered, 0)) AS 'Mar',
        SUM(IF(MONTH(bookings.booking) = 'Apr', bookings.tendered, 0)) AS 'Apr',
        SUM(IF(MONTH(bookings.booking) = 'May', bookings.tendered, 0)) AS 'May',
        SUM(IF(MONTH(bookings.booking) = 'Jun', bookings.tendered, 0)) AS 'Jun',
        SUM(IF(MONTH(bookings.booking) = 'Jul', bookings.tendered, 0)) AS 'Jul',
        SUM(IF(MONTH(bookings.booking) = 'Aug', bookings.tendered, 0)) AS 'Aug',
        SUM(IF(MONTH(bookings.booking) = 'Sep', bookings.tendered, 0)) AS 'Sep',
        SUM(IF(MONTH(bookings.booking) = 'Oct', bookings.tendered, 0)) AS 'Oct',
        SUM(IF(MONTH(bookings.booking) = 'Nov', bookings.tendered, 0)) AS 'Nov',
        SUM(IF(MONTH(bookings.booking) = 'Dec', bookings.tendered, 0)) AS 'Dec',
        SUM(tendered) AS total

UPDATED

select `a`.`month` as `month_int`, IFNULL(SUM(bookings.tendered), 0) as total from `bookings` right join (
SELECT 1 as month
UNION SELECT 2 as month
UNION SELECT 3 as month
UNION SELECT 4 as month
UNION SELECT 5 as month
UNION SELECT 6 as month
UNION SELECT 7 as month
UNION SELECT 8 as month
UNION SELECT 9 as month
UNION SELECT 10 as month
UNION SELECT 11 as month
UNION SELECT 12 as month\n
) a  on `a`.`month` = MONTH(bookings.booking) where `bookings`.`paid` = ? and date(`bookings`.`booking`) > ? and `salon_id` in (?, ?, ?) group by `a`.`month`

Upvotes: 2

Views: 1681

Answers (2)

Dau_uaD
Dau_uaD

Reputation: 88

This code will work 

SELECT m.MONTH as month, 
CASE 
    WHEN SUM(b.total) > 0 THEN SUM(b.total) 
    ELSE 0 END as total
FROM
(
 SELECT '01' AS
 MONTH
 UNION SELECT '02' AS
 MONTH
 UNION SELECT '03' AS   
 MONTH
 UNION SELECT '04' AS
 MONTH 
 UNION SELECT '05' AS
 MONTH
 UNION SELECT '06' AS
 MONTH
 UNION SELECT '07' AS
 MONTH
 UNION SELECT '08' AS
 MONTH
 UNION SELECT '09' AS
 MONTH
 UNION SELECT '10' AS
 MONTH
 UNION SELECT '11' AS
 MONTH
 UNION SELECT '12' AS
 MONTH
 ) AS m
LEFT JOIN
(SELECT MONTH(b.bookings) AS 'month', SUM(b.tendered) AS 'total' 
 FROM bookings AS b
 INNER JOIN salons AS s 
 ON s.id = b.salon_id
 WHERE b.paid = 1
 AND YEAR(b.booking) = 2017) AS n
ON 
m.MONTH = n.month
GROUP BY 1
ORDER BY 1;

Upvotes: 1

arnolem
arnolem

Reputation: 946

You can see this example online : http://sqlfiddle.com/#!9/8bbf0/1

SELECT
  idMonth,
  MONTHNAME(STR_TO_DATE(idMonth, '%m')) as m,
  IFNULL(sum(Bookings.price), 0) as total
FROM Bookings
RIGHT JOIN (
  SELECT 1 as idMonth
  UNION SELECT 2 as idMonth
  UNION SELECT 3 as idMonth
  UNION SELECT 4 as idMonth
  UNION SELECT 5 as idMonth
  UNION SELECT 6 as idMonth
  UNION SELECT 7 as idMonth
  UNION SELECT 8 as idMonth
  UNION SELECT 9 as idMonth
  UNION SELECT 10 as idMonth
  UNION SELECT 11 as idMonth
  UNION SELECT 12 as idMonth
) as Month
ON Month.idMonth = month(`date`)
GROUP BY Month.idMonth

Upvotes: 2

Related Questions