Unknown column ‘Rating’ in ‘having clause’

SELECT
  r.Restaurant_ID,
  r.Name
FROM Restaurant r
JOIN Review rv on r.Restaurant_ID = rv.Restaurant_ID
HAVING AVG(Rating) > Rating
ORDER BY Restaurant_ID;

Copy response:

Error Code: 1054. Unknown column ‘Rating’ in ‘having clause’

I will leave my full code below:

CREATE TABLE Restaurant (
  Restaurant_ID INT NOT NULL,
  Name varchar(45) NOT NULL,
  Cuisine_Type varchar(45) NOT NULL,
  Address varchar(45) NOT NULL,
  City varchar(45) NOT NULL,
  Phone_Number varchar(45) NOT NULL,
  Dine_Type varchar(45) NOT NULL,
  PRIMARY KEY (Restaurant_ID)
);

INSERT INTO Restaurant VALUES ('1', 'MC Donald´s', 'Fastfood', 'Street', 'Cork', '111-1111', 'Yes');

SELECT * FROM Restaurant;

CREATE TABLE Customer (
  Customer_ID INT NOT NULL,
  Name varchar(45) NOT NULL,
  Address varchar(45) NOT NULL,
  Age decimal NOT NULL,
  Gender varchar(45) NOT NULL,
  PRIMARY KEY (Customer_ID)
);

INSERT INTO Customer VALUES ('1', 'Ryu', 'Street', '30', 'Male');

SELECT * FROM Customer;

CREATE TABLE Review (
  Review_ID INT NOT NULL,
  Restaurant_ID INT NOT NULL,
  Customer_ID INT NOT NULL,
  Rating varchar(45) NOT NULL,
  Date varchar(45) NOT NULL,
  PRIMARY KEY (Review_ID),
  FOREIGN KEY (Restaurant_ID) REFERENCES Restaurant (Restaurant_ID),
  FOREIGN KEY (Customer_ID) REFERENCES Customer (Customer_ID)
);

INSERT INTO Review VALUES ('1', '1', '1', '5', '01/01/2016');

SELECT * FROM Review;

SELECT COUNT(Restaurant_ID) , Dine_Type FROM Restaurant WHERE LOWER(City) = 'cork';

SELECT r.Restaurant_ID, r.Name FROM Restaurant r JOIN Review rv on r.Restaurant_ID = rv.Restaurant_ID HAVING AVG(Rating) > Rating ORDER BY Restaurant_ID;

SELECT c.Name , COUNT(Review_ID) From Customer c JOIN Review rv ON c.customer_ID = rv.customer_ID ORDER BY c.Name;

SELECT r.Name,r.Address,r.Cuisine_Type,rv.Date,rv.Rating,c.Name FROM Restaurant r JOIN Review rv ON r.Restaurant_ID = rv.Restaurant_ID JOIN Customer ON rv.customer_ID = c.customer_ID ORDER BY c.Name;

SELECT r.Name FROM Restaurant r JOIN Review rv ON r.Restaurant_ID=rv.Restaurant_ID WHERE rv.Rating >= 8 AND rv.Date LIKE %2016% AND rv.Date LIKE %2017% GROUP BY r.City HAVING COUNT(Review_ID) > 20 ORDER BY r.Name LIMIT 10;

It seems you want to select all rows which have Rating higher than the average Rating.

You cannot do this with standard aggregation because once you have aggregated, the original rows simply do not exist anymore, only the aggregation.

In your case, you have HAVING with no GROUP BY, that implies GROUP BY () (grouping by the empty set), and then you only have a single row. So the Rating column is simply not available without using an aggregation function.

You could use window functions in MySQL 8.0+:

SELECT *
FROM (
    SELECT
      r.Restaurant_ID,
      r.Name,
      Rating,
      AVG(Rating) OVER () AvgRating
    FROM Restaurant r
    JOIN Review rv on r.Restaurant_ID = rv.Restaurant_ID
) r
WHERE AvgRating > Rating
ORDER BY Restaurant_ID;

Alternatively, in SQL Server, you could also use TOP (50) PERCENT, although this gives you the median, not the mean.

SELECT *
FROM (
    SELECT TOP (50) PERCENT
      r.Restaurant_ID,
      r.Name,
      Rating
    FROM Restaurant r
    JOIN Review rv on r.Restaurant_ID = rv.Restaurant_ID
    ORDER BY Rating DESC
) r
ORDER BY Restaurant_ID;

It seems that you have 3 ratings:

• From one user for one restaurant
• An average for a restaurant
• An overall average for all restaurants.

Give this a try:

SELECT  Restaurant_ID, Name
    FROM  
        ( SELECT  r.Restaurant_ID,
                  r.Name, 
                  ( SELECT  AVG(Rating)
                    FROM  Review
                    WHERE  Restaurant_ID = r.Restaurant_ID 
                  ) AS rate1
            FROM  Restaurant AS r 
        ) AS x
    JOIN  
        ( SELECT  AVG(Rating) AS avg_rate
            FROM  Review 
        ) AS overall
    WHERE  x,rate1 > overall.avg_rate
    ORDER BY  Restaurant_ID;

As for the title question, note that I went to a subquery to avoid such an error.

The querie first calculate the total rating of each restaurant and then return restaurant who have rating < AVGRating

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

select r.Restaurant_ID, r.Name, max(r.rate) as rate 
  from (select r.Restaurant_ID, r.Name, Rating, AVG(Rating)  OVER (PARTITION BY r.Restaurant_ID ORDER BY r.Restaurant_ID ROWS UNBOUNDED PRECEDING) rate,
AVG(Rating) OVER () AvgRating FROM Restaurant r  
JOIN Review rv on r.Restaurant_ID = rv.Restaurant_ID ) r 
WHERE AvgRating > r.rate 
ORDER BY Restaurant_ID;



+---------------+--------------+------+
| Restaurant_ID | Name         | rate |
+---------------+--------------+------+
|             1 | MC Donald´s  |  5.5 |
+---------------+--------------+------+
1 row in set (0.00 sec)