Home Using case in a aggregate sql query without splitting rows
Reply: 3

Using case in a aggregate sql query without splitting rows

user3248331
1#
user3248331 Published in 2017-11-14 08:20:46Z

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

lad2025
2#
lad2025 Reply to 2017-11-14 08:54:47Z

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
Sahi
3#
Sahi Reply to 2017-11-14 08:29:13Z

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
Dinesh
4#
Dinesh Reply to 2017-11-14 08:44:48Z

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.303815 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO