Home How Do I collapse rows on null values in t-sql?
Reply: 3

How Do I collapse rows on null values in t-sql?

Zman25
1#
Zman25 Published in 2017-11-13 15:26:42Z

I'm in a weird situation with my query. My objective is to display the total deposits and withdrawals from multiple transactions for each person and display them. I am getting multiple rows that I need to collapse into one. This all needs to happen in one query

SELECT
       lastname,
       firsname,
       case when upper(category) = 'W' then sum(abs(principal)) end as Withdrawal,
       case when upper(category) = 'D' then sum(abs(principal)) end as Deposit,
       description
FROM
       table1 
       JOIN table2 ON table1.id = table2.id 
       JOIN table3 ON table2.c = table3.c 
WHERE 
       description = 'string'
GROUP BY
       lastname,
       firstname,
       description,
       category

my result is

 lastname    firstname    Withdrawal    Deposit    description
 john         smith       null           140.34    string
 john         smith       346.00          null     string
 jane         doe         null           68.03     string
 jane         doe         504.00          null     string

and I am looking for

 lastname    firstname    Withdrawal    Deposit    description
 john         smith       346.00        140.34     string
 jane         doe         504.00        68.03      string

adding principal into the group does not work. any help on solving this will be greatly appreciated!

Gordon Linoff
2#
Gordon Linoff Reply to 2017-11-13 15:28:06Z

Use conditional aggregation . . . the case is the argument to the sum():

select lastname, firsname,
       sum(case when upper(category) = 'W' then abs(principal) end) as Withdrawal,
       sum(case when upper(category) = 'D' then abs(principal) end) as Deposit, 
       description
from table1 join
     table2
     on table2.id = table1.id join
     table3 
     on table3.c = table2.c
where description = 'string'
group by lastname, firstname, description
Md Nazmul
3#
Md Nazmul Reply to 2017-11-13 15:55:47Z

Solution using Subquery

select t.lastname, t.firsname,sum(t.Withdrawal) Withdrawal ,sum(t.Deposit) Deposit,t.description from(
select lastname, firsname,
       isnull(case when upper(category) = 'W' then abs(principal) end,0) as Withdrawal,
       isnull(case when upper(category) = 'D' then abs(principal) end,0) as Deposit,
description
from table1 join
     table2
     on table2.id = table1.id join
     table3
     on table3.c = table2.c
where description = 'string'
)t group by t.lastname, t.firstname, t.description, t.category
Sambhu
4#
Sambhu Reply to 2017-11-14 12:00:34Z

TRY this

SELECT lastname,firsname, SUM(case when upper(category) = 'W' then abs(principal) end) as Withdrawal, SUM(case when upper(category) = 'D' then abs(principal) end) as Deposit, description FROM table1 JOIN table2 ON table1.id = table2.id JOIN table3 ON table2.c = table3.c WHERE description = 'string' GROUP BY lastname,firstname,description

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO