running sum with case statements by group

user_15974340 0
Viewed 109

I want to count running sum,but my code doesn't work, some ideas why?

select id, color, time,  (sum(case when question = 1 then 1 else 0 end) OVER (PARTITION BY id ORDER BY time rows) + 
 sum(case when suggestion='True' then 1 else 0 end)  OVER (PARTITION BY id ORDER BY time) + 
sum(case when proposal= 'True' then 1 else 0 end)  OVER (PARTITION BY id ORDER BY time)) as s
from table

Here is sample data

id       color   time   question    suggestion    proposal 
1         pink    14:00    0          True         False
1         red     15:00    0          False        False
1         blue    13:00    0           False       False
2         green   11:00    0          True         False
2         orange  15:00    1           False       False

Result:

id       color   time      s
1         pink    14:00    1         
1         red     15:00    1         
1         blue    13:00    0           
2         green   11:00    1          
2         orange  15:00    2
cumulative-sum window-functions boolean sum postgresql
1 Answers

You need to use SUM() window function only once, if you convert all the boolean expressions to integers:

SELECT id, color, time,
       SUM((question = 1)::int + suggestion::int + proposal::int) OVER (PARTITION BY id ORDER BY time) s
FROM tablename
ORDER BY id, time;

See the demo.

user_104988280
Related