CODEWITHSUNDEEP
mysqlsql
xpanta
xpanta

Reputation: 8418

mysql, order by subquery count

I am trying to do this:

SELECT 
    id, user_id, roi, 
    (select count(id) from main_ub U where U.user_id=W.user_id and U.cons > 0) as COUNT 
FROM 
    main_stats W 
WHERE 
    week=43 and year=2013 and votes > 2 and COUNT >= 20
ORDER BY 
    roi desc
LIMIT 1

but I always get this error:

#1054 - Unknown column 'COUNT' in 'where clause'

Is it possible to use the inner select in my WHERE clause?

Upvotes: 1

Views: 1406

Answers (2)

Ashish Gaur
Ashish Gaur

Reputation: 2050

SELECT * FROM 

(SELECT 
    id, user_id, roi, 
    (select count(id) from main_ub U where U.user_id=W.user_id and U.cons > 0) as COUNT 
FROM 
    main_stats W 
WHERE 
    week=43 and year=2013 and votes > 2) res 

WHERE res.COUNT >= 20
ORDER BY 
    res.roi desc
LIMIT 1

Upvotes: 2

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

You cannot use aliases in your WHERE clause. You have to use the entire expression, like this:

SELECT id,
    user_id,
    roi,
    (
        SELECT count(id)
        FROM main_ub U
        WHERE U.user_id = W.user_id
            AND U.cons > 0
        ) AS COUNT
FROM main_stats W
WHERE week = 43
    AND year = 2013
    AND votes > 2
    AND (
        SELECT count(id)
        FROM main_ub U
        WHERE U.user_id = W.user_id
            AND U.cons > 0
        ) >= 20
ORDER BY roi DESC LIMIT 1

Upvotes: 1

Related Questions