CREATE TABLE T(amount INT, index INT); INSERT INTO T VALUES (55, 1),(88, 5),(45, 6),(86, 7),(87, 10), (88, 11),(57, 16),(58, 17),(59, 20),(60, 21); You can take advantage of Postgres function generate_series().
generate_series(start, stop, step) Generate a series of values, from start to stop with a step size of step
The example below uses a series from 0 to 14 with an interval of 4.
SELECT generate_series(0, 14, 4) Serie; | serie | | ----: | | 0 | | 4 | | 8 | | 12 | WITH CTS AS ( SELECT generate_series(0, 14, 4) Serie ) SELECT COALESCE(SUM(amount),0) AS amount, CTS.Serie FROM CTS LEFT JOIN T ON index >= CTS.Serie AND index < CTS.Serie + 4 GROUP BY CTS.Serie ORDER BY CTS.Serie; amount | serie -----: | ----: 55 | 0 219 | 4 175 | 8 0 | 12 You can use a user defined function that allows you to use parameters
CREATE OR REPLACE FUNCTION GroupIntervals(low int, high int, step int) RETURNS TABLE (amount int, interv int) AS $$ BEGIN RETURN QUERY WITH CTS AS ( SELECT generate_series(low, high, step) Serie ) SELECT COALESCE(SUM(T.amount),0)::int AS amount, CTS.Serie as interv FROM CTS LEFT JOIN T ON index >= CTS.Serie AND index < CTS.Serie + step GROUP BY CTS.Serie ORDER BY CTS.Serie; END; $$ LANGUAGE plpgsql;✓
SELECT * FROM GroupIntervals(0, 14, 4);amount | interv -----: | -----: 55 | 0 219 | 4 175 | 8 0 | 12
SELECT * FROM GroupIntervals(5, 14, 5);amount | interv -----: | -----: 219 | 5 175 | 10
dbfiddle here