如何按月分组，如果某个月没有值，则返回零？

Question

这是我的mysql收入表。

+----+------------------+---------------------------+------------+---------+
| 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 |
+----+------------------+---------------------------+------------+---------+

字段“日期”是标准sql日期。我执行此查询的目的是按月计算收入总和，如果某个月没有收入，则返回零。如果某个月没有任何收入，我想要零，因为我想在图表中显示这些数据。

这是查询。

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

但是我只会得到如下输出。如果其他月份没有值，则没有零。这是输出。

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

我希望上表中的其他月份和total_num为零。我怎样才能做到这一点？那里也有同样的问题。但没有有效的答案。
按月分组，如果找不到数据，则返回0

请帮我解决这个问题。我用于此应用程序的语言是Node.JS :)

Answer 1

也许这不是最好的方法，但是它将解决您的问题。快速介绍：

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