CODEWITHSUNDEEP
sqlpostgresql
davidbaguetta
davidbaguetta

Reputation: 532

How to sum rows in groups of 3?

I have a table that looks like this:

id | amount
1  | 8
2  | 3
3  | 9
3  | 2
4  | 5
5  | 3
5  | 1
5  | 7
6  | 3
7  | 3
8  | 5

I need a query that returns the summed amount of rows grouped by every 3 consequent IDs. The result should be:

ids (not a necessary column, just to explain better) | amount
1,2,3 | 22
4,5,6 | 19
7,8 | 8

In my table, you can assume IDs are always consequent. So there can't be a 10 without existing a 9 too. But the same ID can also show up multiple times with different amounts (just like in my example above).

Upvotes: 0

Views: 242

Answers (1)

xQbert
xQbert

Reputation: 35323

Assuming ID is a numeric data type. Demo

SELECT max(id) maxID,  SUM(Amount) as Amount
FROM TBLNAME 
GROUP BY Ceiling(id/3.0) 
ORDER BY maxID

Giving us:

+-------+--------+
| maxid | amount |
+-------+--------+
|     3 |     22 |
|     6 |     19 |
|     8 |      8 |
+-------+--------+

Doc Link: Ceiling

  • MaxID is included just so the order by makes sense and validation of totals can occur.
  • I used 3.0 instead of 3 and implicit casting to a decimal data type (a hack I know but it works) otherwise integer math takes place and the rounding when the division occurs provides a incorrect result.

Without the .0 on the 3.0 divisor we'd get:

+-------+--------+
| maxid | amount |
+-------+--------+
|     2 |     11 |
|     5 |     27 |
|     8 |     11 |
+-------+--------+

Ceiling() is used over floor() since floor() would not allow aggregation of 1-3 in the same set.

Upvotes: 3

Related Questions