Cumulative (Running) Sum

[Matt Culbreth <mattculbreth@xxxxxxxxx>]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general