Subtracting 2 columns grouped by column

user_15367109 0
Viewed 33

I have this table where each subject can have all days of year :

subject date_num value
A        123      5
B        123      3
C        123      1
A        567      9
B        567      8
C        567      3

For dates query between 123 and 567 I need to get :

subject trend
A        -4
B        -5
C        -2

(I get 2 rows for each subject, one for date 123, the other for date 567, and I need to subtract them, but one of the subjects may not have a certain date - then ignore this subject)

Tried this which failed :

    WITH RNG AS(
        SELECT date_num, subject, value
        FROM history
        WHERE (date_num = 1546300800 OR date_num=1635292800)
        ORDER BY date_num
    )
    SELECT date_num, subject, value,
    SUM(value) AS trend // here subtract first date value from second date value
    FROM RNG
postgresql sql
1 Answers

You can use a self-join:

select t1.subject, t1.value - t2.value 
from subjects t1 join subjects t2 on t1.subject = t2.subject and t2.date_num = 567 where t1.date_num = 123
user_73267380
Related