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|
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
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.