MySQL - 2 个总和和 2 个 WHERE 语句从一个表到 2 列

Question

也许这对某人来说是一个简单的答案，但我搜索了又搜索但失败了。这是我所拥有的:1 个包含以下内容的表:

id, int
year, int
month, int
day, int
revenue, float
sub_category, text

我想要得到的是 1 个 SQL 语句，该语句返回 1 个包含 3 列的表，用于保存每月收入和 sub_category 的总和

-------------------------------
| Month | revcomp1 | revcomp2 |
-------------------------------
|   01  | sumcat1  | sumcat2  |
-------------------------------
|   02  | sumcat1  | sumcat2  |
-------------------------------
|   ..  |    ..    |    ..    |

这是我运行的 SQL 语句:

SELECT 
 month,
 cast(sum(rev *-1) as decimal(10,2)) as REVCOMP1,
 0 as REVCOMP2
FROM 
 tbl_data
WHERE
 year='2014' and sub_category='comp1'
GROUP BY month, sub_category DESC

UNION

SELECT 
 month,
 0 as REVCOMP1
 cast(sum(rev *-1) as decimal(10,2)) as REVCOMP2,
FROM 
 tbl_data
WHERE
 year='2014' and sub_category='comp2'
GROUP BY month, sub_category DESC

但这并没有真正在一行中返回给定月份的总和......

非常感谢任何帮助，谢谢

Answer 1

您可以使用条件聚合来做到这一点:

select month,
       cast(sum(case when sub_cateogry = 'comp1' then rev *-1 end) as decimal(10,2)) as REVCOMP1,
       cast(sum(case when sub_category = 'comp2' then rev *-1 end) as decimal(10,2)) as REVCOMP2
from tbl_data t
where year = '2014'
group by month;