SQL gets the number of records that satisfy various conditions

Question

The goal is to get the number of users in one table who have:

• field EXPIREDATE> CURRENT_TIMESTAMP as nUsersActive
• field EXPIREDATE <CURRENT_TIMESTAMP as nUsersExpired
• field EXPIREDATE NULL as nUsersPreregistered

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

nUsersActive nUsersExpired nUsersPreregistered 10 2 15 

it will be later json_encoded and passed to ExtJS script for display.

Any hint? I tried several times without succeeding. I tried using the UNION operator, I get the correct numbers, but, of course, in the column, while I need them in the line.

Thanks for your support.

Answer 1

Something like the following should work, you may need to configure 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 the lines (this is not so 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 assume you are using SQL Server. You should be able to get what you are looking for using the CASE statement. Make sure you return something (something) 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 will 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