CODEWITHSUNDEEP
mysqlsqldategroup-bymariadb
One SNapp
One SNapp

Reputation: 65

Counting all rows in column with two different date conditions

I'm trying to turn two count queries with date conditions (the ones below) into one query. 

SELECT COUNT(*) as yesterday FROM orders WHERE DATE(timedate) = DATE(NOW() - INTERVAL 1 DAY)
SELECT COUNT(*) as yesterday FROM orders WHERE DATE(timedate) = DATE(NOW() - INTERVAL 2 DAY)

Following the advice of another answer I created the following, but that doesn't seem to work syntax-wise, and I'm not quite sure why. Is there another way to do this? I can't find a similar question on this

SELECT 
    SUM(IF(DATE(timedate) = DATE(NOW() - INTERVAL 1 DAY))) AS testcount1,
    SUM(IF(DATE(timedate) = DATE(NOW() - INTERVAL 2 DAY))) AS testcount2
FROM 
    orders

Upvotes: 0

Views: 312

Answers (2)

GMB
GMB

Reputation: 222492

You want conditional aggregation. I would phrase the query as follows:

SELECT 
    SUM(
        timedate >= CURRENT_DATE - INTERVAL 1 DAY
        and timedate < CURRENT_DATE
    ) AS testcount1,
    SUM(
        timedate >= CURRENT_DATE - INTERVAL 2 DAY
        and timedate < CURRENT_DATE- INTERVAL 1 DAT
    ) AS testcount2
FROM orders

Details:

  • this uses a nice feature of MySQL, that evaluates false/true conditions as 0/1 in numeric context

  • no date functions are applied on the timedate column : instead, we do litteral date comparisons. This is much more efficient, since the database can possibly take advantage of an index on the datetime column

You might also want to add a WHERE clause to the query:

WHERE 
    timedate >= CURRENT_DATE - INTERVAL 2 day
    AND timedate< CURRENT_DATE

Upvotes: 1

Nick
Nick

Reputation: 147196

You're missing the output values for the IF expression. Also you should use CURRENT_DATE() so you don't need to convert to a DATE:

SELECT 
    SUM(IF(DATE(timedate) = CURRENT_DATE() - INTERVAL 1 DAY, 1, 0)) AS testcount1,
    SUM(IF(DATE(timedate) = CURRENT_DATE() - INTERVAL 2 DAY, 1, 0)) AS testcount2
FROM 
    orders

Note that MySQL treats boolean expressions as 1 (true) or 0 (false) in a numeric context, so you can actually SUM the expression without needing the IF:

SELECT 
    SUM(DATE(timedate) = CURRENT_DATE() - INTERVAL 1 DAY) AS testcount1,
    SUM(DATE(timedate) = CURRENT_DATE() - INTERVAL 2 DAY) AS testcount2
FROM 
    orders

Upvotes: 1

Related Questions