CODEWITHSUNDEEP
mysqlsql
Scott Bartell
Scott Bartell

Reputation: 2840

MySQL group by with multiple column sums and a total sum for each group

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;

Upvotes: 7

Views: 28908

Answers (5)

Himanshu S Shankhala
Himanshu S Shankhala

Reputation: 61

SELECT person, 
       sum(positive_vote) as totalPositive, 
       sum(negative_vote) as totalNegative,
       (sum(positive_vote + negative_vote)) as totalVotes
FROM Votes 
GROUP BY person

Upvotes: 0

John Woo
John Woo

Reputation: 263693

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

Upvotes: 19

xdazz
xdazz

Reputation: 160833

Do you mean the sum of positive_vote and 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;

Upvotes: 1

lc.
lc.

Reputation: 116448

If you want the total for each person, just subtract the sums (or add them instead if you just want a total number of 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 sums here 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.

Upvotes: 4

Teja
Teja

Reputation: 13524

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;

Upvotes: 2

Related Questions