CODEWITHSUNDEEP
sqlsql-serversql-server-2008
DharaPPatel
DharaPPatel

Reputation: 12743

Using case and sum the values in SQL

i have started working on SQL but i kind of forgot some of the ways to show data. i have tables like this :

drop table  #temptable
create table #temptable
(
    Id int,
    [Type] int,
    Balance numeric(10,2)
)

INSERT INTO #TempTable (ID, [Type], Balance) values (103,1,500)
INSERT INTO #TempTable (ID, [Type], Balance) values (103,3,156)
INSERT INTO #TempTable (ID, [Type], Balance) values (103,4,790)
INSERT INTO #TempTable (ID, [Type], Balance) values (103,6,345)
INSERT INTO #TempTable (ID, [Type], Balance) values (103,1,450)
INSERT INTO #TempTable (ID, [Type], Balance) values (103,2,50)
INSERT INTO #TempTable (ID, [Type], Balance) values (104,1,100)
INSERT INTO #TempTable (ID, [Type], Balance) values (104,5,500)

select * from #temptable

i have the above data in the temporary table. The result query should do group by the ID and sum the balance where the [Type] is 1 and 3 in one column named Type1Balance and rest [Type] Balance should be sum in another column named Type2Balance. Can anybody please help with this query. Thanks in advance.

Upvotes: 0

Views: 2183

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use conditional aggregation based on types.

select id, 
sum(case when [Type] in (1,3) then Balance else 0 end) type1bal,
sum(case when [Type] not in (1,3) then Balance else 0 end) type2bal
from tablename
group by id

Upvotes: 6

Related Questions