Question

0

172

Views

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?

over 4 years ago · Santiago Trujillo

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-05-11T22:03:08.283Z

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.

0

172

SQL get sum of a Column in Select

1 answers

0

Find remote jobs

Andres GPT