postgres Finding the difference between numbers of the same column

Question

I have a table like the below

| date       | key | value |   |
|------------|-----|-------|---|
| 01-01-2009 | a   | 25    |   |
| 01-01-2009  | b   | 25    |   |
| 01-01-2009  | c   | 10    |   |

I'm trying to come up with a query which would allow me to do (a+b)-c for each day - but my join is doing this (a+b+c)-c

with total as (
select
    sum(value),
    date
from
    A
where
    key in ('a',
    'b')
group by
    date ) 
select
    sum(total.sum) as a, sum(A.value) as b,
    sum(total.sum) - sum(A.value) as value,
        A.date
    from
        A
    join total on
        total.date = A.date
    where
        A.key = 'c'
    group by
        A.date

This is giving me a value of 50 (it should be 40) - my C values are getting calcualted as part of the total table during the join

What am i doing wrong?

Are you always guaranteed to have A, B, and C values for all dates? How would you expect to handle cases that don't have all values (or have other values like D, E or F)? — Zack
– Zack, Commented Jun 18, 2019 at 20:14
@Zack - not everyday no , Sometimes either of these values could be null — Jayaram
– Jayaram, Commented Jun 18, 2019 at 20:18

Gordon Linoff · Accepted Answer · 2019-06-18 20:09:21Z

3

How about simply doing conditional aggregation?

select date,
       sum(case when key in ('a', 'b') then value 
                when key in ('c') then - value
           end) as daily_calc
from a
group by date;

A join doesn't seem very helpful for this calculation.

answered Jun 18, 2019 at 20:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

The Impaler · Accepted Answer · 2019-06-18 20:11:28Z

3

You can join three table expressions, as in:

select
  a.date,
  (a.value + b.value) - c.value
from (select * from A where key = 'a') a
join (select * from A where key = 'b') b on b.date = a.date
join (select * from A where key = 'c') c on c.date = a.date

answered Jun 18, 2019 at 20:11

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Collectives™ on Stack Overflow

postgres Finding the difference between numbers of the same column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related