Hello Group,
I might have missed this somewhere, but PostgreSQL doesn't presently
support the cumulative/running aggregate function SUM() OVER
(PARTITION BY) syntax that Oracle does, right?
Here's an example of what I'm talking about:
Say we have a table of sales by month & person. We want to query this
table and show both a month's sales AND the cumulative sum for that
person. Something like this:
MONTH PERSON VALUE CUMULATIVE_SUM
-------------------- -------------------- ---------- --------------
January David 50 50
January Matt 10 10
February David 45 95
February Matt 5 15
March David 60 155
March Matt 20 35
In Oracle this is nicely accomplished by using the following syntax:
SELECT
c.Month,
c.Person,
c.Value,
sum(c.value) over(partition by c.Person order by c.Month_Num,
c.Person) as Cumulative_Sum
FROM
CS_Test c
ORDER BY
c.Month_Num ASC,
c.Person ASC
In PostgreSQL however, we can do this, but we have to use a subquery:
SELECT
c.Month,
c.Person,
c.Value,
(select sum(c2.value) from CS_Test c2 where c2.Month_Num <=
c.Month_num and c2.person = c.person) as Cumulative_Sum
FROM
CS_Test c
ORDER BY
c.Month_Num ASC,
c.Person ASC
So is there planned support for the newer syntax or is a subquery the
best/only way to go on PostgreSQL for now?
Thanks,
Matt