Problem with Microsoft SQL Count

Question

Hey smart guys. I am having problems with the following SQL statement. I know that I cannot do GROUP BY in an OnlineStatus column, and that makes sense because it is a function call, not the actual column in my table. How can I change this so that I can calculate the number of users on the network?

SELECT CASE dbo.fnGetWebUserOnlineStatus(W.Id) WHEN 1 THEN 'Online' WHEN 2 THEN 'Ingame' ELSE 'Offline' END AS OnlineStatus FROM dbo.WebUsers W WHERE W.[Status]=1 GROUP BY OnlineStatus 

Answer 1

This is best done using a subquery:

 SELECT OnlineStatus, count(*) FROM ( SELECT CASE dbo.fnGetWebUserOnlineStatus(W.Id) WHEN 1 THEN 'Online' WHEN 2 THEN 'Ingame' ELSE 'Offline' END AS OnlineStatus FROM dbo.WebUsers W WHERE W.[Status]=1 ) sub GROUP BY OnlineStatus 

Answer 2

It should work if you use internal selection:

 SELECT OnlineStatus, COUNT(*) FROM ( SELECT CASE dbo.fnGetWebUserOnlineStatus(W.Id) WHEN 1 THEN 'Online' WHEN 2 THEN 'Ingame' ELSE 'Offline' END AS OnlineStatus FROM dbo.WebUsers W WHERE W.[Status]=1 ) AS T1 GROUP BY OnlineStatus 

Answer 3

You can also move CASE WHEN to COUNT

Pseudocode:

 SELECT COUNT(CASE dbo.fnGetWebUserOnlineStatus(W.Id) WHEN 1 THEN 1 ELSE NULL END) as OnlineCount, COUNT(CASE dbo.fnGetWebUserOnlineStatus(W.Id) WHEN 2 THEN 1 ELSE NULL END) as IngameCount ... FROM dbo.WebUsers W WHERE W.[Status]=1