CODEWITHSUNDEEP
sqlsql-server-2008
snp.it
snp.it

Reputation: 522

Difference of Total in the same Column

I have this Column,

  ID  Year  ColumnDesc  Amount   
  1    2000   Taken       100
  1    2000  NotTaken    10
  1    2001   Taken       200
  1    2001  NotTaken   100

So i need to do the Difference between the Amounts with same ID and Year so my table should look like.

  ID  Year  ColumnDesc  Amount   
  1    2000   Taken    90
  1    2001   Taken    100

Can any one help me..

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

I think you want conditional aggregation:

select id, year, 'Taken' as ColumnDesc,
       sum(case when ColumnDesc = 'Taken' then Amount
                when ColumnDesc = 'Not Taken' then - Amount
                else 0
           end) as Amount
from t
group by id, year;

Upvotes: 5

Related Questions