CODEWITHSUNDEEP
mysqlsqlselect
BreMea
BreMea

Reputation: 105

SQL get sum of a Column in Select

I've got a table like the one shown below.

+------+-------+
|  id  | value |
+------+-------+
| 1    | 5     |
+------+-------+
| 2    | 9     |
+------+-------+
| 1    | 8     |
+------+-------+
| 3    | 10    |
+------+-------+

I'd like to be able to select three entries with the highest value. However, I want the ids to be grouped and the values to be added together. Here's the desired output:

+------+-------+
|  id  | value |
+------+-------+
| 1    | 13    |
+------+-------+
| 3    | 10    |
+------+-------+
| 2    | 9     |
+------+-------+

Currently, I've have this code: SELECT * FROM table GROUP BY id ORDER BY SUM(value) DESC LIMIT 3 It is able to pick the top three rows (sorted by the value column) and group them by the ids. However, it returns with the highest value instead of adding them together, as seen below.

+------+-------+
|  id  | value |
+------+-------+
| 1    | 8     |
+------+-------+
| 3    | 10    |
+------+-------+
| 2    | 9     |
+------+-------+

What should I use to add together the value column on my SELECT statement?

Upvotes: 1

Views: 47

Answers (1)

The Impaler
The Impaler

Reputation: 48780

You can do:

select *
from (
  select id, sum(value) as total from t group by id
) x
order by total desc
limit 3

Or... in compressed syntax:

select id, sum(value) as total from t group by id order by total desc limit 3

See running example at DB Fiddle.

Upvotes: 3

Related Questions