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?

Answer

SELECT state, ANY_VALUE(number), candidate, SUM(votes) AS tt_votes   
FROM table   
GROUP BY state, candidate     
ORDER BY state, candidate;

Advertisement

Answer