'How to SUM values of a column and then order by another column using SQL?
When I SELECT * FROM table; I have the following result:
+-------+--------+-------------+-------+
| state | number | candidate | votes |
+-------+--------+-------------+-------+
| AR | 12 | CANDIDATE A | 9 |
| AR | 12 | CANDIDATE A | 23 |
| LA | 12 | CANDIDATE A | 19 |
| OK | 12 | CANDIDATE A | 2 |
| TX | 12 | CANDIDATE A | 7 |
| TX | 12 | CANDIDATE A | 7 |
| AR | 25 | CANDIDATE B | 2 |
| LA | 25 | CANDIDATE B | 5 |
| LA | 25 | CANDIDATE B | 1 |
| OK | 25 | CANDIDATE B | 17 |
| OK | 25 | CANDIDATE B | 21 |
| TX | 25 | CANDIDATE B | 7 |
| LA | 17 | CANDIDATE C | 14 |
| LA | 17 | CANDIDATE C | 42 |
| OK | 17 | CANDIDATE C | 13 |
| OK | 17 | CANDIDATE C | 5 |
| TX | 17 | CANDIDATE C | 1 |
| TX | 17 | CANDIDATE C | 4 |
+-------+--------+-------------+-------+
So I try to sum the total of votes with SUM(votes) as tt_votes and then group the result by candidate to have a desc order.
SELECT state, number, name, SUM(votes) as tt_votes
FROM table
GROUP BY candidate
ORDER BY tt_votes DESC;
+-------+--------+-------------+----------+
| state | number | candidate | tt_votes |
+-------+--------+-------------+----------+
| LA | 17 | CANDIDATE C | 79 |
| AR | 12 | CANDIDATE A | 67 |
| TX | 25 | CANDIDATE B | 53 |
+-------+--------+-------------+----------+
state column shows the first (or the last?) record for the candidate, but what I'd really like to have is not only group by candidates, but also group by state first to have a result like this:
+-------+--------+-------------+----------+
| state | number | candidate | tt_votes |
+-------+--------+-------------+----------+
| AR | 12 | CANDIDATE A | 32 |
| AR | 25 | CANDIDATE B | 2 |
| AR | 17 | CANDIDATE C | 0 |
| LA | 17 | CANDIDATE C | 56 |
| LA | 12 | CANDIDATE A | 19 |
| LA | 25 | CANDIDATE B | 6 |
| OK | 25 | CANDIDATE B | 38 |
| OK | 17 | CANDIDATE C | 18 |
| OK | 12 | CANDIDATE A | 2 |
| TX | 12 | CANDIDATE A | 14 |
| TX | 25 | CANDIDATE B | 7 |
| TX | 17 | CANDIDATE C | 5 |
+-------+--------+-------------+----------+
It lists the states in alphabetic order and then the tt_votes of each candidate in each state order by tt_votes.
Tried UNION or inserting WHERE state = AR for example, but without success.
Any ideas how it could be done?
EDIT
If I use (SELECT state, ANY_VALUE(number), candidate, SUM(votes) as tt_votes WHERE state = 'AR' GROUP BY state, number, candidate) UNION ((SELECT state, ANY_VALUE(number), candidate, SUM(votes) as tt_votes WHERE state = 'LA' GROUP BY state, number, candidate) UNION (SELECT state, ANY_VALUE(number), candidate, SUM(votes) as tt_votes WHERE state = 'OK' GROUP BY state, number, candidate); etc. I have what we need. But is there a better way to do this?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
