Chanaka De Silva Chanaka De Silva - 4 months ago 23
SQL Question

How to group by month and return zero if no value for certain month?

This is my mysql income table.

+----+------------------+---------------------------+------------+---------+
| id | title | description | date | amount |
+----+------------------+---------------------------+------------+---------+
| 1 | Vehicle sales up | From new sale up | 2016-09-09 | 9999.99 |
| 2 | Jem 2 Sales | From rathnapura store | 2016-05-15 | 9545.25 |
| 3 | Jem 2 Sales 2 | From rathnapura store | 2016-05-15 | 9545.25 |
| 4 | Jem 2 Sales 2 | From rathnapura store 234 | 2016-05-15 | 9545.25 |
+----+------------------+---------------------------+------------+---------+


The field 'date' is standard sql date. And I executed this query in order to take sum of incomes by month and return zero if no income from a certain month. I want zeros if no income from a certain month because i want to display these data in a chart.

This is the query.

SELECT MONTHNAME(`date`) AS mName, MONTH(`date`) AS mOrder, ifnull(sum(amount),0) AS total_num FROM income GROUP BY mOrder ORDER BY mOrder DESC


But I only get a output like follows. No zeros if no values in other months. This is the output.

+-----------+--------+-----------+
| mName | mOrder | total_num |
+-----------+--------+-----------+
| September | 9 | 9999.99 |
| May | 5 | 28635.75 |
+-----------+--------+-----------+


And I want other months in above table and total_num as zero. How can I do this? There's same kind of question there too. But no working answer.
Group by month and return 0 if data not found

Please help me to solve this issue. The language I use for this application is Node.JS :)

Answer

Maybe this it's not the best way to do it, but it will solve your problem. As a quick soution:

SELECT 'January' AS mName, 1 AS mOrder, COALESCE(SUM(amount),0) AS total_num 
FROM income i
WHERE month(i.date) = 1

UNION

SELECT 'February' AS mName, 2 AS mOrder, COALESCE(SUM(amount),0) AS total_num 
FROM income i
WHERE month(i.date) = 2

UNION

...and go on
Comments