I want to sum a few values using case statements. My problem is compiler complains if I do not add a group by to the end of the query, which then that negates my Distinct, hah. For example, in my sample below this returns 2 entries for red12 even though 1 entry does not fit any of the criteria above, but the Group By messes me up! How can I run this query and only return applicable results, i.e. what the case statements show?

Create Table #Bobsled
  [event] varchar(100)
  ,[time] decimal(18,4)
  ,employeeID varchar(25)
  ,name varchar(500)

Insert Into #Bobsled Values
('Walk', '16.32','red12', 'red arrow')
,('Eat', '5.12', 'red12', 'red arrow')
,('Run', '32.13','pink01', 'pink pig')
,('Walk', '2.12', 'bl81', 'blue fire')
,('Sleep', '8.12', 'gr99', 'green pony')

Select Distinct
,case when [event] = 'Walk' then SUM([time]) else 0 end as walktime
,case when [event] = 'RUN' then SUM([time]) else 0 end as runtime
,case when [event] = 'Sleep' then SUM([time]) else 0 end as sleeptime
FROM #Bobsled
--only executes sucesfully with this 
GROUP BY employeeID, [event]

You should use the Pivot operator for this query. See the below.

      [event] VARCHAR(100)
    , [time] DECIMAL(18, 4)
    , employeeID VARCHAR(25)
    , name VARCHAR(500)

VALUES  ( 'Walk', '16.32', 'red12', 'red arrow' )
,       ( 'Eat', '5.12', 'red12', 'red arrow' )
,       ( 'Run', '32.13', 'pink01', 'pink pig' )
,       ( 'Walk', '2.12', 'bl81', 'blue fire' ) 
,       ( 'Sleep', '8.12', 'gr99', 'green pony' );

WITH    CTE_Bobsled
          AS ( SELECT   *
               FROM     @Bobsled B PIVOT ( SUM([time]) FOR [event] IN ( [Walk],
                                                              [Run], [Sleep] ) ) AS PivotTable
    SELECT  employeeID
          , Walk
          , Run
          , Sleep
    FROM    CTE_Bobsled;
| improve this answer | |

I think what you're looking for is an aggregate on employeeID where the CASE goes inside the SUM() aggregate function.

SELECT employeeid,
       SUM((CASE WHEN [event] = 'Walk' THEN [time] ELSE 0 END)) AS walktime,
       SUM((CASE WHEN [event] = 'RUN' THEN [time] ELSE 0 END)) AS runtime,
       SUM((CASE WHEN [event] = 'Sleep' THEN [time] ELSE 0 END)) AS sleeptime
FROM #Bobsled
GROUP BY employeeID;

This way, you get one result row per employeeID, basically pivoted on event.

| improve this answer | |
  • 1
    The ELSE 0 is there to avoid aggregating on NULL values, which is ugly and will produce a warning. – Daniel Hutmacher Jan 28 '16 at 16:09
  • 1
    Yes, if there are zero rows with event='Walk' for some empoyees, the sum of all nulls will be null. The ELSE 0 avoids that. – ypercubeᵀᴹ Jan 28 '16 at 16:32
  • 1
    A condition WHERE [event] IN ('Walk', 'Run', 'Sleep') might be added as well. It might improve efficiency (or make it worse ;). – ypercubeᵀᴹ Jan 28 '16 at 16:35
  • What's wrong with aggregating on NULL values? For SUM there's a correct alternative to NULL, but there is not for AVG. NULL is the correct value in this case. AVG((CASE WHEN [event] = 'Walk' THEN [time] ELSE ??? END)) – Carl Leth Jan 28 '16 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.