FDavidov FDavidov - 6 months ago 8
SQL Question

SQL query (in MySql)

Hello to all and specially SQL GURUs,

I have a table with potentially very many records (above 1 million) that contains simple data along with a timestamp that holds the moment of registration (defining CURRENT_TIMESTAMP as the default value for that field).

I want to implement a simple query that scans the table only once and counts the number of records that were register, say, the last 24 hours, the last 7 days and the last month.

Was trying this with no results (getting all zeros though I should get few 1's for sure):

SET @1 = CURRENT_TIMESTAMP() ;
SET @2 = @1 - INTERVAL 24 HOUR ;
SET @3 = @1 - INTERVAL 7 DAY ;
SET @4 = @1 - INTERVAL 1 MONTH;

select Registration_Timestamp , (case Registration_Timestamp when Registration_Timestamp> @2 THEN 1 else 0 end) as LAST_DAY ,
(case Registration_Timestamp when Registration_Timestamp> @3 THEN 1 else 0 end) as LAST_WEEK ,
(case Registration_Timestamp when Registration_Timestamp> @4 THEN 1 else 0 end) as LAST_MONTH
from tbl_Dummy
where Registration_Timestamp >= @4 ;


This is not going to give the sum of course, but once I get this to show
1
s I will wrap it with
select count(LAST_DAY),count(LAST_WEEK),count(LAST_MONTH)
which will do the job of course.

Where is my error here?

Thanks in advance for any help.

Answer

Your CASE statements are slightly out:

 CASE *expr1* 
    WHEN *expr2* THEN *expr3*
    ELSE 0 
 END

Compares expr1 to expr2 and returns expr3 when expr1 = expr2

Your first CASE actually compares Registration_Timestamp (a timestamp) to the result of Registration_Timestamp> @2 (1 for true, 0 for false) which isn't what you want.

You want this form of CASE:

CASE  
    WHEN *expr1* THEN *expr2*
    ELSE 0 
END

Which returns expr2 when expr1 is true

In your first CASE:

CASE WHEN Registration_Timestamp > @2 THEN 1 ELSE 0 END

Alternatively

MySQL actually returns 1 and 0 already for conditionals. It's not hugely portable but you could use:

SELECT Registration_Timestamp > @2 AS LAST_DAY

Note

COUNT(*expr*) will count 1 for every NOT NULL result, which will count 1s and 0s the same, so you'd need SUM() here.

Or you could change your CASEs to:

CASE WHEN Registration_Timestamp > @2 THEN 1 END

Which will return NULL when the condition is not met.

Comments