PostgreSQL query for intervals of 1 min with accumulated sum over all the timeframe

Question

I'm struggling with this query so far i can create the intervals of 1 min but the sum of the two columns that I need is given for just that min and not the accumulated sum over time

SELECT
    TIMESTAMP WITH TIME ZONE 'epoch' +
    INTERVAL '1 second' * round(extract('epoch' from "Timestamp") / 60) * 60 as Timestamp",
    SUM("Particles"), sum("Bio")
from "Results" 
where "SampleID" = 50 
GROUP BY round(extract('epoch' from "Timestamp") / 60)
ORDER BY "Timestamp" DESC

that is the query that i'm using and the results are these

          Timestamp      |Sum |Sum
"2013-08-09 14:17:00-07" | 61 | 4
"2013-08-09 14:16:00-07" | 64 | 6
"2013-08-09 14:15:00-07" | 29 | 5
"2013-08-09 14:14:00-07" | 96 | 1
"2013-08-09 14:13:00-07" | 43 | 2

but i need the accumulative sum of those last two colums

Select
    TIMESTAMP WITH TIME ZONE 'epoch' +
    INTERVAL '1 second' * round(extract('epoch' from "Timestamp") / 60) * 60 as "Timestamp",
    sum("Particles") over (order by "Timestamp") as Cumulative_Part, 
    sum("Bio") over (order by "Timestamp") as Cumulative_Bio from "Results" 
where
    "SampleID" = 50 and
    "Timestamp" between '2013-08-09 14:13:00' and '2013-08-09 14:17:00'
GROUP BY round(extract('epoch' from "Timestamp") / 60)
Order by "Timestamp" DESC

Yeah it work i just had to change it a little bit thanks !! – Ubaldo Quintero Aug 20 '13 at 22:33 — Ubaldo Quintero, Aug 20 '13 at 22:33

score 0 · Accepted Answer · edited Aug 20 '13 at 21:31

0

If you have postgresql 8.4 or above, you can use a OVER(ORDER BY..) construct

SELECT...
, SUM("Particles") OVER(ORDER BY TimeStamp_Field) as SUM_Particles
...

see here for more info

edited Aug 20 '13 at 21:31

bma

9,424
2
33
22

answered Aug 20 '13 at 21:11

Declan_K

6,726
2
19
30

I get an error saying "column "Results.Particles" must appear in the GROUP BY clause or be used in an aggregate function" – Ubaldo Quintero Aug 20 '13 at 21:22
Please add the new full SQL statement to your orignal question. It's hard to tell what is going on just from your comment. – Declan_K Aug 20 '13 at 21:46
I know that the problem is something to do with the way i get the interval, because i try your answer without getting the interval and works fine but i need to separate the timestamp in 1 min intervals – Ubaldo Quintero Aug 20 '13 at 21:50

PostgreSQL query for intervals of 1 min with accumulated sum over all the timeframe

1 Answers1