CODEWITHSUNDEEP
mysql
Erich García
Erich García

Reputation: 1874

Add a WHERE in COUNT(*) condition MySQL

I have a SQL Query which retrieve the average rating of rating table joined with a ads table.

My query works fine, But I want to add a WHERE statement to get only the ads which get two or more ratings.

This is my query:

SELECT ad_id, AVG(rating) as average_rating,
COUNT(user_id) as num_rates
FROM ads_rating
GROUP BY ad_id

And my desired query:

SELECT ad_id, AVG(rating) as average_rating,
COUNT(user_id) as num_rates
FROM ads_rating
WHERE num_rates > 1
GROUP BY ad_id

But MariaDB is telling me:
[Err] 1054 - Unknown column 'num_rates' in 'where clause'

Thanks and sorry my noob in MySQL

Upvotes: 0

Views: 914

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

You can't use column aliases in the WHERE So you need to do. 

SELECT ad_id, AVG(rating) as average_rating,
COUNT(user_id) as num_rates
FROM ads_rating
WHERE COUNT(user_id) > 1
GROUP BY ad_id

Or better using HAVING works with the GROUP BY

SELECT ad_id, AVG(rating) as average_rating,
COUNT(user_id) as num_rates
FROM ads_rating
GROUP BY ad_id
HAVING COUNT(user_id) > 1

Upvotes: 3

Related Questions