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]