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).
Advertisement
Answer
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 | +-------+--------+
- 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.