CODEWITHSUNDEEP
sqlpostgresql
SMW
SMW

Reputation: 339

How to choose the first value then ignor rest?

I have a table with data as follows:

id  activity  amount   
1    unknown   20           
2    storage   20          
3    storage   20          
4    swift     20          
5    delivery  50          
6    storage   20

I want to create a query which gives me the "calculated" sum. for the example above.. the desired result is:

id  activity  amount   calculatedsum
1    unknown   20           0
2    storage   20          20
3    storage   20          20
4    swift     20          20
5    delivery  50          70         (had 20 and 50 arrived)
6    storage   20          70

the logic is simple.. find the first row which is 'storage', that is the calculatedsum. when encounter a row with 'delivery' add it and that is the new calculatedsum.

This is what I tried to do:

select *,
       sum(case when activity  = 'Storage' then amount
                when activity  = 'delivery' then + amount
                else 0
           end)  over (order by id)
from  A

however this doesn't work...

how can I get the expected result?

Edit: id is a colum which was created by: select row_number() over (order by .... nulls last) as id the table contains the result from the query... and eveytime the query runs the table is reseted by it... so the id is always the actual row number.

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

If you are only counting the first 'storage', then you need to identify it. You can do that using row_number():

select *,
       sum(case when activity = 'Storage' and seqnum = 1 then amount
                when activity  = 'delivery' then amount
                else 0
           end) over (order by id)
from (select a.*,
             row_number() over (partition by activity order by id) as seqnum
      from A a
     ) a

A totally weird way of doing this without a subquery, if we assuming that the amounts for storage are all the same:

select *,
       (sum(case when activity  = 'delivery' then amount
                 else 0
             end) over (order by id) + 
         min(case when activity = 'storage' then amount else 0
             end) over (order by id)
       )
from A a;

Upvotes: 0

Related Questions