0

For this, I have the query:

SELECT r.restaurantName, COUNT(m.itemNo), AVG(m.price)
FROM Restaurant r
INNER JOIN MenuItem m
ON m.restaurantNo = r.restaurantID
INNER JOIN Dish d
ON d.dishNo = m.dishNo;

It generates this result:

+----------------+-----------------+--------------+
| restaurantName | COUNT(m.itemNo) | AVG(m.price) |
+----------------+-----------------+--------------+
| Tasty Thai     |              12 |    11.479167 |
+----------------+-----------------+--------------+

So, it is displaying the correct average, however, it has picked ALL menu items I added to the table instead of just for each individual restaurant, as well as counting all of the items. It also is only displaying one of the three restaurants I inserted.

How do I fix this?

Database code:

CREATE TABLE Restaurant(restaurantID Integer Primary Key, restaurantName Char(15) NOT NULL, type Char(15) NOT NULL, city char(15) NOT NULL);

CREATE TABLE Dish(dishNo Integer Primary Key, dishName Char(15) NOT NULL, type ENUM ('ap', 'en', 'ds'));

CREATE TABLE MenuItem(itemNo Integer Primary Key, restaurantNo int, dishNo int, price DECIMAL(4,2) CHECK(price IN(price>=5 and price<=50)), FOREIGN KEY(restaurantNo) REFERENCES Restaurant(restaurantID) ON DELETE NO ACTION, FOREIGN KEY(dishNo) REFERENCES Dish(dishNo) ON DELETE SET NULL);

CREATE TABLE FoodOrder(orderNo Integer Primary Key, itemNo int, dateof date CHECK(dateof IN(dateof>='2017-01-01')), timeof time, FOREIGN KEY(itemNo) REFERENCES MenuItem(itemNo) ON DELETE CASCADE);
1

You are missing GROUP BY:

SELECT r.restaurantName, COUNT(m.itemNo), AVG(m.price)
FROM Restaurant r
INNER JOIN MenuItem m
ON m.restaurantNo = r.restaurantID
INNER JOIN Dish d
ON d.dishNo = m.dishNo
GROUP BY r.restaurantName; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.