CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bypivot
Giovanni Rivas
Giovanni Rivas

Reputation: 91

SQL Server : grouping 2 columns in same row

I have got this query 

select 
    Id,
    case when isThirdParty = 0 then sum(total) end as FirstPartyFees,
    case when isThirdParty = 1 then sum(total) end as ThirdPartyFees
from 
    MyTable
group by 
    id, isThirdParty

And I got this result 

Id                                    FirstPartyFees                          ThirdPartyFees
------------------------------------ --------------------------------------- ---------------------------------------
DA29BDC0-BE3F-4193-BFDC-493B354CE368 15.00                                   0.00
2EF0B590-FE4F-42E8-8426-5864A739C16B 5.00                                    0.00
246DC3D8-732F-4AE3-99F3-BDEBF98F7719 15.00                                   0.00
FC81F220-ED54-48FE-AE1B-C394492E82A4 5.00                                    0.00
336D9CF1-6970-48BA-90E5-C7889914DDCB 114.00                                  0.00
6F2EEF6F-5FA1-42E5-A988-DB88037DAB92 5.00                                    0.00
80763B37-68E1-4716-B32A-FE82C1700B52 15.00                                   0.00
DA29BDC0-BE3F-4193-BFDC-493B354CE368 0.00                                    1.00
2EF0B590-FE4F-42E8-8426-5864A739C16B 0.00                                    1.00
246DC3D8-732F-4AE3-99F3-BDEBF98F7719 0.00                                    1.00
FC81F220-ED54-48FE-AE1B-C394492E82A4 0.00                                    1.00
336D9CF1-6970-48BA-90E5-C7889914DDCB 0.00                                    0.00
6F2EEF6F-5FA1-42E5-A988-DB88037DAB92 0.00                                    1.00
80763B37-68E1-4716-B32A-FE82C1700B52 0.00                                    1.00

As you see, I get duplicates for the first party and the third party. How can I group first party and third party total for the same Id in 1 row?

Upvotes: 0

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Because isThirdParty is an indicator, you can also just use math:

select Id, SUM(total * (1 - isThirdParty)) as FirstPartyFees,
   SUM(total * isThirdParty) as ThirdPartyFees
frOm MyTable
group by id;

Upvotes: 2

GMB
GMB

Reputation: 222652

You are looking for conditional aggregation.

You should move the case expression inside the sum() and remove isThirdParty from the group by clause.

SELECT Id,
    SUM(CASE WHEN isThirdParty = 0 THEN total END) AS FirstPartyFees,
    SUM(CASE WHEN isThirdParty = 1 THEN total END) AS ThirdPartyFees
FROm MyTable
GROUP BY id

Upvotes: 5

Related Questions