sum every 3 rows of a table

group-by, postgresql, rows, sum, window-functions

Solution

This is not so tricky with `lag()` window function (also on SQL Fiddle):

CREATE TABLE t ("date" timestamptz, "count" int4);
INSERT INTO t VALUES
('2012-06-21 05:20:00',12),
('2012-06-21 05:21:00',14),
('2012-06-21 05:22:00',10),
('2012-06-21 05:23:00',20),
('2012-06-21 05:24:00',25),
('2012-06-21 05:25:00',30),
('2012-06-21 05:26:00',10);

SELECT *,
    "count"
    + coalesce(lag("count", 1) OVER (ORDER BY "date"), 0)
    + coalesce(lag("count", 2) OVER (ORDER BY "date"), 0) AS "total"
  FROM t;

- I've double-quoted `date` and `count` columns, as these are reserved words;

- `lag(field, distance)` gives me the value of the `field` column `distance` rows away from the current one, thus first function gives previous row's value and second call gives the value from the one before;

- `coalesce()` is required to avoid `NULL` result from `lag()` function (for the first row in your query there's no “previous” one, thus it's `NULL`), otherwise the `total` will also be `NULL`.

Problem

I have the following query to count all data every minute. ``` $sql= "SELECT COUNT(*) AS count, date_trunc('minute', date) AS momento FROM p WHERE fk_id_b=$id_b GROUP BY date_trunc('minute', date) ORDER BY momento ASC"; ``` What I need to do is get the sum of the count for each row with the count of the 2 past minutes. ``` For example with the result of the $sql query above |-------date---------|----count----| |2012-06-21 05:20:00 | 12 | |2012-06-21 05:21:00 | 14 | |2012-06-21 05:22:00 | 10 | |2012-06-21 05:23:00 | 20 | |2012-06-21 05:24:00 | 25 | |2012-06-21 05:25:00 | 30 | |2012-06-21 05:26:00 | 10 | I want this result: |-------date---------|----count----| |2012-06-21 05:20:00 | 12 | |2012-06-21 05:21:00 | 26 | 12+14 |2012-06-21 05:22:00 | 36 | 12+14+10 |2012-06-21 05:23:00 | 44 | 14+10+20 |2012-06-21 05:24:00 | 55 | 10+20+25 |2012-06-21 05:25:00 | 75 | 20+25+30 |2012-06-21 05:26:00 | 65 | 25+30+10 ```

Original source