Using case and sum the values in SQL

Question

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.

Vamsi Prabhala · Accepted Answer · 2016-06-08 19:57:28Z

6

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

answered Jun 8, 2016 at 19:57

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

4 Comments

DharaPPatel Over a year ago

Thanks, this working perfectly fine, but i have one question that if we have many more fields in the select then how would that work ?

Vamsi Prabhala Over a year ago

you would have to group by all of them. Or have this as a sub-query and join it to the required tables on id to get other columns in the result if they can't be grouped by.

DharaPPatel Over a year ago

This query requires me three tables and outer query will also required to be joined again to fetch some fields so is it okay to repeat the tables in sub query and main query ?

Vamsi Prabhala Over a year ago

it is okay to do that.

Collectives™ on Stack Overflow

Using case and sum the values in SQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related