I have a simple table with date and budget values
| budget | date |
|---|---|
| 10 | 2022-05-01 |
| 5 | 2022-05-02 |
| 15 | 2022-05-03 |
| 16 | 2022-05-04 |
| 22 | 2022-05-05 |
| 13 | 2022-05-06 |
| 23 | 2022-05-07 |
| 56 | 2022-05-08 |
I want to group every N rows in one with PostgreSQL statement.
I expect to see something like this, if N = 2 (for example, it also can be 3 or 5, it depends)
| budget | date |
|---|---|
| 15 | 2022-05-01 – 2022-05-02 |
| 31 | 2022-05-03 – 2022-05-04 |
| 35 | 2022-05-05 – 2022-05-06 |
| 79 | 2022-05-07 – 2022-05-08 |
If it more that 2, the date should be formatted as start_row_date – end_row_date (2022-05-01 – 2022-05-03 for N = 3)
>Solution :
We can try to use ROW_NUMBER window function with some math which to represent N if your "date" might be datatype we can try to use TO_CHAR to get the format as your expected.
SELECT SUM(budget) budget,
CONCAT(MIN("date"),'-',MAX("date")) date
FROM (
SELECT *,ROW_NUMBER() OVER(ORDER BY "date") rn
FROM T
) t1
GROUP BY (rn - 1)/2
ORDER BY (rn - 1)/2