Microsoft SQL Count problem

Question

Hey smarties. I'm having trouble with the following SQL statement. I know that I can't do a GROUP BY on the OnlineStatus column, and it makes sense because it's a function call, not an actual column in my table. How would I modify this so that I can get a count of how many users are online?

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

That's 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 an inner select:

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 could also move the CASE WHEN into the COUNT

Pseudo code:

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