如果某个月份没有值，如何按月分组并返回零? [英] How to group by month and return zero if no value for certain month?

问题描述

这是我的mysql收入表.

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

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

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.

这是查询.

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

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

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

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

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

推荐答案

也许这不是最好的方法，但它会解决您的问题.作为一个快速的解决方案:

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

这篇关于如果某个月份没有值，如何按月分组并返回零?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！