CODEWITHSUNDEEP
sqlsql-servert-sql
user3248331
user3248331

Reputation: 105

Using case in a aggregate sql query without splitting rows

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

Upvotes: 1

Views: 107

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175796

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

Upvotes: 2

Dinesh
Dinesh

Reputation: 112

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

Upvotes: 0

Sahi
Sahi

Reputation: 1484

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

Upvotes: 1

Related Questions