MySQL query from subquery not working

Question

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

@Quassnoi, updated with the error message - not sure how helpful it is though. — James Goodwin
– James Goodwin, Commented Mar 25, 2010 at 13:02

Quassnoi · Accepted Answer · 2010-03-25 13:19:17Z

3

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.

edited Mar 25, 2010 at 13:19

answered Mar 25, 2010 at 12:56

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

James Goodwin Over a year ago

@Quassnoi Should it make a difference when using CASE instead of IF?

Quassnoi Over a year ago

@James: No, this is just more legible. Which MySQL version do you use? Try adding AS before the query alias, this may be required for the older versions.

James Goodwin Over a year ago

Version is 4.0.21, just found out that it doesn't support subqueries :(

Quassnoi Over a year ago

@James: MySQL 4.0 does not support nested queries. You should use COUNT(*) instead.

Collectives™ on Stack Overflow

MySQL query from subquery not working

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related