MySQL 两个查询的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6685517/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sum totals of two queries
提问by This Guy
I have two basic queries which I need to sum the totals of:
我有两个基本查询，我需要对以下查询求和：
Select hours, sum(hours) FROM table WHERE name='xxx' and Description='Worked'
Select hours2, sum(hours2) FROM table WHERE name='xxx' and Description2='Worked'
I've tried UNION and it will get me the totals of each query but it will not combine them.
我试过 UNION ，它会给我每个查询的总数，但它不会组合它们。
Table setup is:
表设置是：
- ID
- name
- hours
- description
- hours2
- description2
- ID
- 姓名
- 小时
- 描述
- 小时 2
- 说明2
I need to correlate hours to description and hours2 to description2 which is why I have the two different queries. I need to sum the totals of hours and hours2.
我需要将 hours 与 description 和 hours2 与 description2 相关联，这就是为什么我有两个不同的查询。我需要将小时数和小时数相加。
回答by a1ex07
First of all, you missed group by, so even though mysql doesn't complain about it, you hoursand hours2values are meaningless.
Secondly, you the result of UNIONcan be put in derived subquery, so you will have the desired total :
首先，您错过了group by，因此即使 mysql 没有抱怨它，您hours和hours2值也毫无意义。其次，您的结果UNION可以放在派生子查询中，因此您将获得所需的总数：
SELECT SUM(hr) FROM
(
Select sum(hours) as hr FROM table WHERE name='xxx' and Description='Worked'
UNION ALL
Select sum(hours2) as hr FROM table WHERE name='xxx' and Description2='Worked'
)a
回答by GarethD
You would need to place your union into a subquery:
您需要将联合放入子查询中：
SELECT Hours,
SUM(Hours) AS Hours,
SUM(Hours2) AS Hours2
FROM ( SELECT Hours,
SUM(Hours) AS Hours,
0 AS Hours2
FROM Table
WHERE Name = 'xxx'
AND Description = 'Worked'
GROUP BY Hours
UNION ALL
SELECT Hours2,
0 AS Hours,
SUM(Hours2) AS Hours
FROM Table
WHERE Name = 'xxx'
AND Description2 = 'Worked'
GROUP BY Hours2
) t
GROUP BY Hours;
