Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
employeeid
,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]
share|improve this question
up vote 4 down vote accepted

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

DECLARE @Bobsled TABLE
    (
      [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' );

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;
share|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.

share|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 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 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 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 at 23:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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