Aggregate values by interval range given by parameter

I have a table containing an amount column and an index column. I wish to build a single query, which sums up the amount for the given interval ranges for every resulting interval from given start index to end index - i.e. the 'interval range' slices the data according index column. For these groups, sum over amount is built.
Is this possible and how?

``````SELECT sum(amount) from mytable
WHERE index between <startindex> and <endindex>
GROUP BY ...
``````

Example:

``````amount | index
55        1
88        5
45        6
86        7
87        10
88        11
57        16
58        17
59        20
60        21
``````

A query with interval range 5, start index 5 and endindex 14 should result in two return values:

``````  88+45+86 => 219
87+88    => 175
``````

A query with interval range 4, start index 0 and endindex 15 should result in four return values:

``````  55       => 55
88+45+86 => 219
87+88    => 175
--       => 0
``````

Thanks for any help!

``````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(T.amount),0) AS amount, CTS.Serie
FROM      CTS
LEFT JOIN T
ON        T.index >= CTS.Serie
AND       T.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        T.index >= CTS.Serie
AND       T.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

Update

As Evan Carrol has pointed out on him comments you can avoid use CTE and change function language from plpgsql to plain SQL.

``````CREATE OR REPLACE FUNCTION GroupIntervals(low int, high int, step int)
RETURNS TABLE (amount int, interv int)
AS \$\$
SELECT    COALESCE(SUM(T.amount),0)::int AS amount, CTS.Serie as interv
FROM      generate_series(low, high, step) AS CTS(Serie)
LEFT JOIN T
ON        index >= CTS.Serie
AND       index <  CTS.Serie + step
GROUP BY  CTS.Serie
ORDER BY  CTS.Serie;
\$\$ LANGUAGE sql;
``````
``````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

• Incredible answer, thank you for your work! Jan 18, 2018 at 12:44
• I'm glad to help Jan 18, 2018 at 14:00
• @McNets Good answer, but a couple minor points: I wouldn't wrap the `GENERATE_SERIES` in a CTE, and I wouldn't use plpgsql for this. This is just regular `SQL`. Jan 18, 2018 at 16:44
• @McNets dbfiddle.uk/… (feel free to incorporate that into the answer) Jan 18, 2018 at 16:48
• In the updated answer/fiddle: Where comes CTS from? It would be great, if you could explain that. Thank you!! Jan 23, 2018 at 7:33