CODEWITHSUNDEEP
mysql
James Goodwin
James Goodwin

Reputation: 7406

MySQL query from subquery not working

I am trying to return a number based on the count of results from a table and to avoid having to count the results twice in the IF statement I am using a subquery. However I get a syntax error when trying to run the query, the subquery I have tested by itself runs fine.

Any ideas what is wrong with the query? The syntax looks correct to me

SELECT IF(daily_count>8000,0,IF(daily_count>6000,1,2))
FROM (
    SELECT count(*) as daily_count
    FROM orders201003
    WHERE DATE_FORMAT(date_sub(curdate(), INTERVAL 1 DAY),"%d-%m-%y") =
    DATE_FORMAT(reqDate,"%d-%m-%y")
) q

Error message I get is:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT count(*) as daily_count FROM orders201003

Upvotes: 2

Views: 897

Answers (1)

Quassnoi
Quassnoi

Reputation: 425813

SELECT  CASE WHEN daily_count > 8000 THEN 0 WHEN daily_count > 6000 THEN 1 ELSE 2 END
FROM    (
        SELECT  count(*) as daily_count
        FROM    orders201003
        WHERE   DATE_FORMAT(date_sub(curdate(), INTERVAL 1 DAY),"%d-%m-%y") =
                DATE_FORMAT(reqDate,"%d-%m-%y")
        ) AS q

Also note that the nested queries are only supported starting from MySQL 4.1.

Upvotes: 3

Related Questions