SQL get number of entries satisfying different conditions

Question

Hi,

the goal is to retrieve the number of users in one table which have:

1. field EXPIREDATE > CURRENT_TIMESTAMP as nUsersActive
2. field EXPIREDATE < CURRENT_TIMESTAMP as nUsersExpired
3. field EXPIREDATE IS NULL as nUsersPreregistered

all with one query, and the result should for example be

nUsersActive    nUsersExpired     nUsersPreregistered
10              2                 15

this will later be json_encoded and passed to an ExtJS script for displaying.

Any hint? I tried several times without succeding. I tried with the UNION statement, I get the right numbers, but of course in column, while I need them in row.

Thanks for your support.

Answer 1

Something like the following should work, you may need to adjust for the specific database that you are using.

To get them in columns:

select
    count(case when EXPIREDATE > CURRENT_TIMESTAMP then 1 end) AS nUsersActive,
    count(case when EXPIREDATE > CURRENT_TIMESTAMP then 1 end) AS nUsersExpired,
    count(case when EXPIREDATE IS NULL then 1 end) AS nUserPreregistered
from users_table

And in rows (this is not as efficient!):

  select 
    'nUsersActive' AS Param
    count(case when EXPIREDATE > CURRENT_TIMESTAMP then 1 end) AS Value
  from users_table

UNION ALL

  select 'nUsersExpired',
    count(case when EXPIREDATE > CURRENT_TIMESTAMP then 1 end)
  from users_table

UNION ALL

  select 'nUserPreregistered',
    count(case when EXPIREDATE IS NULL then 1 end)
  from users_table

Answer 2

I'm assuming you are using SQL Server. You should be able to get what you're looking for by using a CASE statement. Make sure you return something (anything) if the condition is true and NULL if the condition is false. Here is the msdn documentation: http://msdn.microsoft.com/en-us/library/ms181765.aspx

Your query would look something like this:

select COUNT(CASE WHEN @ThingToCheck = 'Value' THEN 1 ELSE NULL END) as Count1, COUNT(CASE WHEN @ThingToCheck = 'Value' THEN 1 ELSE NULL END) FROM ....

Answer 3

SELECT COUNT(CASE WHEN EXPIREDATE > CURRENT_TIMESTAMP THEN 1 END) AS nUsersActive,
       COUNT(CASE WHEN EXPIREDATE < CURRENT_TIMESTAMP THEN 1 END) AS nUsersExpired,
       COUNT(CASE WHEN EXPIREDATE IS NULL             THEN 1 END) AS nUsersPreregistered
  FROM Users