Ethan Allen Ethan Allen - 2 months ago 8
MySQL Question

How do I subtract two results from a SELECT statement within that statement?

This pulls back two int values of yesterday and today. I'd like to subtract the two results from within the statement in a third column called difference:

SELECT (
SELECT COUNT(*)
FROM collectors_users
WHERE DATE(dateadded) = CURDATE() - INTERVAL 1 DAY
) AS yesterday, COUNT(*) AS today
FROM collectors_users
WHERE DATE(dateadded) = CURDATE()

Answer

You need to repeat the expressions. SQL (in general) does not allow you to re-use column aliases in the same SELECT. You can simplify the logic to:

SELECT SUM(DATE(dateadded) = CURDATE() - INTERVAL 1 DAY) AS yesterday,
       SUM(DATE(dateadded) = CURDATE()) as today,
       (SUM(DATE(dateadded) = CURDATE()) - 
        SUM(DATE(dateadded) = CURDATE() - INTERVAL 1 DAY)
       ) as diff
FROM collectors_users
WHERE dateadded >= CURDATE() - INTERVAL 1 DAY AND
      dateadded < CURDATE() + INTERVAL 1 DAY;

Note that the logic for the WHERE clause covers two days. Also, it does not use DATE(). This would allow the query to use an index, if available.

Comments