MySQL 分组依据多列总和以及每组的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12632638/
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
MySQL group by with multiple column sums and a total sum for each group
提问by Scott Bartell
I have a table like this:
我有一张这样的表：
Votes (id, person, positive_vote, negative_vote)
I want to group by person and and sort by total votes for each person. I know how to get the total sum of a single column for a group, but I can't figure out how to get the total of all the sum for each group (the total votes).
我想按人分组，然后按每个人的总票数排序。我知道如何获得一组单列的总和，但我无法弄清楚如何获得每个组的所有总和（总票数）。
Here's what I have so far:
这是我到目前为止所拥有的：
SELECT person, sum(positive_vote), sum(negative_vote) FROM Votes GROUP BY person;
回答by John Woo
Try,
尝试，
SELECT person,
sum(positive_vote) totalPositive,
sum(negative_vote) totalNegative,
(sum(positive_vote) + sum(negative_vote)) totalVotes
FROM Votes
GROUP BY person
-- HAVING (sum(positive_vote) + sum(negative_vote)) < 5
回答by Teja
SELECT Z.person,Z.sum_pv,Z.sum_nv,Z.diff_sum_pv_nv
FROM
(SELECT person, sum(positive_vote) AS sum_pv, sum(negative_vote) sum_nv,sum(positive_vote) - sum(negative_vote) AS diff_sum_pv_nv
FROM Votes GROUP BY person)Z;
回答by lc.
If you want the total for each person, just subtract the sums (or add them instead if you just want a total numberof votes):
如果你想总的每个人，只是减去的金额（或增加他们，而不是如果你只是想要一个总数量的选票）：
SELECT person, sum(positive_vote), sum(negative_vote),
SUM(positive_vote)-SUM(negative_vote)
FROM Votes
GROUP BY person
Note I have subtracted the sumshere and not summed the difference of the columns themselves because I do not know how you are storing data in your table and NULLs can do funny things with math.
注意我在这里减去了总和而不是对列本身的差异求和，因为我不知道您如何在表中存储数据，而 NULL 可以用数学做有趣的事情。
回答by xdazz
Do you mean the sum of positive_vote and negative_vote?
你是说positive_vote 和negative_vote 的总和吗？
SELECT
person,
SUM(positive_vote) AS positive_votes,
SUM(negative_vote) AS negative_votes,
SUM(positive_vote + negative_vote) AS total_votes
FROM Votes GROUP BY person;
回答by Himanshu S Shankhala
SELECT person,
sum(positive_vote) as totalPositive,
sum(negative_vote) as totalNegative,
(sum(positive_vote + negative_vote)) as totalVotes
FROM Votes
GROUP BY person
