CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlgroup-by
TreasaNGC
TreasaNGC

Reputation: 62

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.

Upvotes: 1

Views: 61

Answers (4)

DineshDB
DineshDB

Reputation: 6193

Try this, hope this helps you... 

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

Upvotes: 0

pas
pas

Reputation: 98

try this

SELECT 
        [ProjectID]
    ,   SUM(CASE WHEN [Level] = 'LVL_1' THEN [TotalCost] END) as [Level1]
    ,   SUM(CASE WHEN [Level] = 'LVL_2' THEN [TotalCost] END) as [Level2]
    ,   SUM([TotalCost]) as TotalCost
FROM (

    SELECT 
            [ProjectID]
        ,   [Level]
        ,   SUM([Cost]) as TotalCost
    FROM 
        [Table]
    GROUP BY
            [ProjectID]
        ,   [Level]

    ) t
GROUP BY
        t.[ProjectID]

Upvotes: 1

Jatin Patel
Jatin Patel

Reputation: 2104

Try this,

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

Upvotes: 1

Cato
Cato

Reputation: 3701

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

Upvotes: 4

Related Questions