SQL error say column from COUNT does not exisit [duplicate]

Question

SELECT c.review, m.category, u.username, i.item, i.item_id, m.cat_id, COUNT(rv.review_id) totalCount
FROM reviews AS c
LEFT JOIN review_vote as rv
   ON c.review_id = rv.review_id
LEFT JOIN users AS u
   ON u.user_id = c.user_id 
LEFT JOIN items AS i
   ON i.item_id = c.item_id
LEFT JOIN master_cat AS m
   ON m.cat_id = i.cat_id
WHERE LENGTH(c.review) > 50 AND m.category = 'Movies' AND totalCount > 2
GROUP BY rv.review_id
ORDER BY RAND()
LIMIT 1

I get this error:

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

I am selecting this column right away, how come it doesn't exist?

I am trying to random select a review that is listed (or voted on 2 or more times) in in table rv. Also I am not sure if I am using GROUP BY correctly?

Answer 1

With aggregate columns (like count()), you must use a HAVING clause to place a condition on them:

SELECT c.review, m.category, u.username, i.item, i.item_id, m.cat_id, COUNT(rv.review_id) totalCount
FROM reviews AS c
LEFT JOIN review_vote as rv
   ON c.review_id = rv.review_id
LEFT JOIN users AS u
   ON u.user_id = c.user_id 
LEFT JOIN items AS i
   ON i.item_id = c.item_id
LEFT JOIN master_cat AS m
   ON m.cat_id = i.cat_id
WHERE LENGTH(c.review) > 50 AND m.category = 'Movies'
GROUP BY rv.review_id
HAVING COUNT(rv.review_id) > 2 -- Added this line!
ORDER BY RAND()
LIMIT 1

Some databases (including mysql) allow you to use the column alias, eg HAVING totalCount > 2, but using the expression works for all databases. ie for mysql (at least), you could code it as:

....
GROUP BY rv.review_id
HAVING totalCouunt > 2
...

but it wouldn't be portable to all other databases.