CODEWITHSUNDEEP
sqlpostgresqlgroup-bypivot
kuwood
kuwood

Reputation: 23

How to calculate the difference of a result set of rows sharing a value?

With the follow result set:

side|timestamp_ms|sum  |
----|------------|-----|
blue|         135| 1000|
red |         135| 1100|
blue|        1943| 2500|
red |        1943| 2900|

How can i calculate the difference of the sums between blue and red sides that share a timestamp_ms?

Ideally the new result set should be something like:

timestamp_ms|diff |
------------|-----|
         135| -100|
        1943| -400|

Upvotes: 2

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

One method is a join:

select tb.timestamp_ms, tb.sum - tr.sum
from t tr join
     t tb
     on tr.timestamp_ms = tb.timestamp_ms
where tr.side = 'red' and tb.side = 'blue';

Another method is conditional aggregation:

select timestamp_ms,
       (sum(t.sum) filter (where t.side = 'blue') -
        sum(t.sum) filter (where t.side = 'red')
       )
from t
group by timestamp_ms

Upvotes: 3

GMB
GMB

Reputation: 222582

You can do conditional aggregation:

select 
    timestamp_ms,
    sum(case side when 'blue' then t.sum when 'red' then -t.sum end) diff
from mytable t
group by timestamp_ms

This would also work if your data has more than one occurence of each side per timestamp_ms.

Side note: sum is a language keyword, hence not a good choice for a column name.

Upvotes: 2

Related Questions