TreasaNGC TreasaNGC - 5 months ago 8
SQL Question

SQL Server Group By result set is creating 2 seperate rows instead of 1 combined row

Here is an example of my current table:

ProjectID Level Cost
--------- --------- ---------
101 LVL_1 1000.00
101 LVL_2 2500.00
102 LVL_1 1500.00
102 LVL_2 3000.00
103 LVL_2 1200.00


When I run the following query:

SELECT
ProjectID,
CASE WHEN Level = 'LVL_1'
THEN Cost
END AS Level1,
CASE WHEN Level = 'LVL_2'
THEN Cost
END AS Level2,
SUM(Cost)
FROM
Table
GROUP BY
Level,
ProjectID


The result set that I am getting is as follows:

ProjectID Level1 Level2 TotalCost
--------- --------- --------- ---------
101 1000.00 NULL 3500.00
101 NULL 2500.00 3500.00
102 1500.00 NULL 4500.00
102 NULL 3000.00 4500.00
103 NULL 1200.00 1200.00


However what I need back is this layout:

ProjectID Level1 Level2 TotalCost
--------- --------- --------- ---------
101 1000.00 2500.00 3500.00
102 1500.00 3000.00 4500.00
103 NULL 1200.00 1200.00


I realise that the problem is the Group by, because I do not want to group by the levels, I only want to group by the ProjectID itself.
However I am not sure how to go about this as I am using the SUM function, any help would be appreciated.
Thanks in advance!

Also just to note, my query is more complex than this. This is a simplified version.

Answer

try

SELECT 
    ProjectID,
    SUM(CASE WHEN Level = 'LVL_1'
         THEN Cost ELSE 0
         END) AS Level1,
    SUM(CASE WHEN Level = 'LVL_2'
         THEN Cost ELSE 0
         END) AS Level2,
    SUM(Cost) 
FROM 
    Table
GROUP BY 

    ProjectID
Comments