Conditional sum on columns in postgresql

Question

Subway data Columns:

Station_id (1,2,3,4,5,6,7,8,1,2,3,4,5,1,2,3,4,5,6,7,1,2,3)
Number of people boarded
Number of people deboarded
Occupancy

Occupancy at current station = Number of people already in the train at previous station + Number of people boarded - Number of people deboarded

I am trying to fill the occupancy column. The issue is that the dataset is for multiple subway trains so station_id changes back to 1 and on that station number of people deboarded is always 0 since it is the station from which train journey begins. I have got no clue on how to do this in postgresql. The occupancy column in the sample image below is empty and needs to be filled

The train journeys are sorted and grouped.

sample data and the expected output would really be helpful. — Vamsi Prabhala
– Vamsi Prabhala, Commented Nov 30, 2017 at 21:15
Please Edit your question and add some sample data in tabular format and the expected output based on that data. Formatted text please, no screen shots. edit your question - do not post code or additional information in comments. — user330315
– user330315, Commented Nov 30, 2017 at 21:16
Do you have train_id you can add to your data set? Do you have timestamps? — PM 77-1
– PM 77-1, Commented Nov 30, 2017 at 21:22
Thanks for the suggestions. I have uploaded a sample data. There is no timestamp column but I created an id column which is a serial — Aman
– Aman, Commented Nov 30, 2017 at 21:40
SQL tables and their IDs aren't really meant to be used like this. But you should be able to do this using a "running total". Maybe this thread would help stackoverflow.com/questions/22841206/… — Jacob H
– Jacob H, Commented Nov 30, 2017 at 21:43

Gordon Linoff · Accepted Answer · 2017-11-30 21:50:42Z

3

You can do this with the difference of the cumulative sums. The trick is identifying the groups, which I'll do by counting the number of times that station_id has been 1 up to that record.

select s.*,
       (sum(boarded) over (partition by grp order by id) -
        sum(deboarded) over (partition by grp order by id)
       ) as occupants
from (select s.*,
             count(*) filter (where station_id = 1) over (order by id) as grp
      from subwaydata s
     ) s;

answered Nov 30, 2017 at 21:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Conditional sum on columns in postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related