CODEWITHSUNDEEP
postgresql
Ashley I.
Ashley I.

Reputation: 137

How to do cumulative sum in PostgreSQL on a resulting column?

I have this PostgreSQL query that grabs total number of signed up users:

SELECT concat(extract(MONTH from created_at),'-', extract(year from created_at)) AS "Month", 
count(case when is_accountant='t' then email end) AS "Total AB Signups",
count(case when is_accountant='f' then email end) AS "Total SMB Signups",
COALESCE(count(case when is_accountant='t' then email end))
+COALESCE(count(case when is_accountant='f' then email end)) as "Total Signups"
from users
group by 1,extract(month from created_at),extract(year from created_at)
HAVING count(case when is_accountant='t' then email end)<>0 
AND count(case when is_accountant='f' then email end)<>0
order by extract(year from created_at),extract(month from created_at);

Which results in something like this:

Month   | Total AB Signups | Total SMB Signups | Total Signups
-------------------------------------------------------------
08-2015 | 2                | 5                 | 7
09-2015 | 4                | 8                 | 12

How would I add a column for the cumulative sum, like below?

Month   | Total AB Signups | Cumulative AB | Total SMB Signups | Cumulative SMB | Total Signups | Cumulative Total
-----------------------------------------------------------------------------------------------------------------
08-2015 | 2                | 2             |5                  | 5              | 7             | 7
09-2015 | 4                | 6             |8                  | 13             | 12            | 19

Upvotes: 0

Views: 1466

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

Try this:

SELECT 
   CONCAT("Month", '-', "Year") AS "Month / Year",
   "Total AB Signups",
   SUM("Total AB Signups") OVER (ORDER BY "Year", "Month") AS "Cumulative AB",
   "Total SMB Signups",
   SUM("Total SMB Signups") OVER (ORDER BY "Year", "Month") AS "Cumulative SMB",
   "Total AB Signups" + "Total SMB Signups" AS "Total Signups",
   SUM("Total AB Signups" + "Total SMB Signups") 
   OVER (ORDER BY "Year", "Month") AS "Cumulative Total"       
FROM (
SELECT EXTRACT(YEAR FROM created_at) AS "Year",
       EXTRACT(MONTH FROM created_at) AS "Month",
       COUNT(CASE WHEN is_accountant='t' THEN email END) AS "Total AB Signups",
       COUNT(CASE WHEN is_accountant='f' THEN email END) AS "Total SMB Signups"
FROM users
GROUP BY 1, EXTRACT(MONTH FROM created_at), EXTRACT(YEAR FROM created_at)
HAVING COUNT(CASE WHEN is_accountant='t' THEN email END) <> 0 
       AND COUNT(CASE WHEN is_accountant='f' THEN email END) <> 0 ) AS t
ORDER BY "Year", "Month"

The idea is simple: apply SUM with OVER (ORDER BY ...) on the derived table produced by the original query.

Upvotes: 1

Related Questions