Using case in a aggregate sql query without splitting rows

Question

In ms-sql i am using an aggregate group to filter results by user, i would also like to use case to filter by a row not contained in the aggregate so all the user results are on 1 line. I'm not sure this is possible.

Here is the query below which splits the results into two lines.

select case  when col_A='1' then sum(col b) End as Sum1_results,
case  when col_A='2' then sum(col_b) End as Sum2_Results, Username from tbl1
group by Username, col_A

example of results is.

Sum1_results | Sum2_results | Username 

 5499          null           John
 null          3400           John

Ideally, i would like to just have these results merged into one line for each username if possible. Any help would be appreciated

Lukasz Szozda · Accepted Answer · 2017-11-14 08:54:47Z

2

You could use:

select Username ,
     SUM(case when col_A='1' then col_b End) as Sum1_results,
     SUM(case when col_A='2' then col_b End) as Sum2_Results, 
from tbl1
group by Username

edited Nov 14, 2017 at 8:54

answered Nov 14, 2017 at 8:23

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

2 Comments

user3248331 Over a year ago

Thanks for the answer and editing the question format, Sahi was first so i have to give him the credit for the answer though.

user3248331 Over a year ago

my apologies, i must of misread the time stamp. I will change yours to the accepted answer

Sahi · Accepted Answer · 2017-11-14 08:29:13Z

1

below Query can do the job

    Create table #tmp (col_A CHAR(1),col_b int,Username VARCHAR(10))

    INSERT INTO #tmp VALUES('1',5000,'John')
    INSERT INTO #tmp VALUES('2',400,'John')
    INSERT INTO #tmp VALUES('1',499,'John')
    INSERT INTO #tmp VALUES('2',3000,'John')

    SELECT * FROM #tmp

    select SUM(case when col_A='1' then col_b End) as Sum1_results,
         SUM(case when col_A='2' then col_b End) as Sum2_Results,Username 
    from #tmp

    group by col_A,UserName

    DROP TABLE #tmp

answered Nov 14, 2017 at 8:29

Sahi

1,4841 gold badge15 silver badges34 bronze badges

Comments

Dinesh · Accepted Answer · 2017-11-14 08:44:48Z

0

Results merged into one line for each username.

Create table #tmp (col_A CHAR(1),col_b int,Username VARCHAR(10))

INSERT INTO #tmp VALUES('1',5000,'John')
INSERT INTO #tmp VALUES('2',400,'John')
INSERT INTO #tmp VALUES('1',499,'John')
INSERT INTO #tmp VALUES('2',3000,'John')

SELECT * FROM #tmp

select SUM(case when col_A='1' then col_b End) as Sum1_results,
     SUM(case when col_A='2' then col_b End) as Sum2_Results,Username 
from #tmp

group by UserName

DROP TABLE #tmp

answered Nov 14, 2017 at 8:44

Dinesh

1129 bronze badges

Collectives™ on Stack Overflow

Using case in a aggregate sql query without splitting rows

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related