CODEWITHSUNDEEP
mysql
user2168066
user2168066

Reputation: 635

Select zero when no results in date range

I have a query that I am using to pull back the total costs per months for the previous 6 months of data. The issue I need to solve is when there is no records for a specific month, nothing is returned and only 5 months are shown.

I need to modify this query to always show the 6 months, even when there is no data for a specific month but I am unsure how to accomplish this.

        select sum(cost),
            CASE
                WHEN MONTH(collection_date) = 1 THEN 'January'
                WHEN MONTH(collection_date) = 2 THEN 'February'
                WHEN MONTH(collection_date) = 3 THEN 'March'
                WHEN MONTH(collection_date) = 4 THEN 'April'
                WHEN MONTH(collection_date) = 5 THEN 'May'
                WHEN MONTH(collection_date) = 6 THEN 'June'
                WHEN MONTH(collection_date) = 7 THEN 'July'
                WHEN MONTH(collection_date) = 8 THEN 'August'
                WHEN MONTH(collection_date) = 9 THEN 'September'
                WHEN MONTH(collection_date) = 10 THEN 'October'
                WHEN MONTH(collection_date) = 11 THEN 'November'
                WHEN MONTH(collection_date) = 12 THEN 'December'
                ELSE 'NULL'
                END AS datemodified
            from invoices
            WHERE collection_date >= DATE_SUB(now(), INTERVAL 5 MONTH)
            GROUP BY MONTH(collection_date)
            ORDER BY collection_date asc;

Sample of the results with an empty month

 COST      Datemodified      
 300       September
 200       November
 200       December

Desired output

 COST      Datemodified
 0         August
 300       September
 0         October
 200       November
 200       December

Upvotes: 2

Views: 65

Answers (1)

Racil Hilan
Racil Hilan

Reputation: 25371

You can create fake month data and join your invoices table to it. Try this:

SELECT SUM(cost), months.name AS datemodified
FROM (SELECT 1 AS num, 'January' AS name
      UNION SELECT 2, 'February'
      UNION SELECT 3, 'March'
      UNION SELECT 4, 'April'
      UNION SELECT 5, 'May'
      UNION SELECT 6, 'June'
      UNION SELECT 7, 'July'
      UNION SELECT 8, 'August'
      UNION SELECT 9, 'September'
      UNION SELECT 10, 'October'
      UNION SELECT 11, 'November'
      UNION SELECT 12, 'December') months
LEFT JOIN invoices.collection_date = months.num
WHERE collection_date >= DATE_SUB(NOW(), INTERVAL 5 MONTH)
GROUP BY MONTH(collection_date)
ORDER BY collection_date ASC;

However, that gives you all the 12 months. To get only the 6 last months, you need to dynamically generate your fake month data:

SELECT SUM(cost),
       CASE num WHEN 1 THEN 'January'
                WHEN 2 THEN 'February'
                WHEN 3 THEN 'March'
                WHEN 4 THEN 'April'
                WHEN 5 THEN 'May'
                WHEN 6 THEN 'June'
                WHEN 7 THEN 'July'
                WHEN 8 THEN 'August'
                WHEN 9 THEN 'September'
                WHEN 10 THEN 'October'
                WHEN 11 THEN 'November'
                WHEN 12 THEN 'December'
       END AS datemodified
FROM (SELECT MONTH(NOW()) AS num
      UNION SELECT MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS num
      UNION SELECT MONTH(DATE_SUB(NOW(), INTERVAL 2 MONTH)) AS num
      UNION SELECT MONTH(DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS num
      UNION SELECT MONTH(DATE_SUB(NOW(), INTERVAL 4 MONTH)) AS num
      UNION SELECT MONTH(DATE_SUB(NOW(), INTERVAL 5 MONTH)) AS num) months
LEFT JOIN invoices.collection_date = months.num
WHERE collection_date >= DATE_SUB(NOW(), INTERVAL 5 MONTH)
GROUP BY MONTH(collection_date)
ORDER BY collection_date ASC;

Upvotes: 2

Related Questions