sql group by count problem

Question

Hi, I have the following query:

select d.restaurant_id, restaurant_category, dish_name, cuisine_id, count(ingredient_id)
  from restaurant r, dish d, composition c
 where r.restaurant_id = d.restaurant_id 
   and d.restaurant_id = c.restaurant_id 
   and d.dish_id = c.dish_id
group by d.restaurant_id
having count(distinct cuisine_id) > 1;

The tables are the following:

• restaurant(restaurant_id, restaurant_category)
• dish(restaurant_id, dish_name, dish_id, cuisine_id)
• composition(restaurant_id, dish_id, ingredient_id)

The query it's returning only one row when it should be returning 3. Any ideas?

Answer 1

You can't use an aggregate function like that in a join. You need to wrap that in a sub query or something.

Answer 2

Suggestion #1, use SQL92 style joins. Suggestion #2, use an alias in all of your column names Suggestion #3, if you want a distinct cuisine_id then make that part of the aggregate.

The main problem here is that your group by is incorrect. All columns that are not part of the aggregate function must appear in the GROUP BY clause. I'm surprised your database ran this.

Here is what it should look like:

SELECT 
    d.restaurant_id, 
    cuisine_id, 
    Count(ingredient_id)  
FROM restaurant r
INNER JOIN dish d ON r.restaurant_id = d.restaurant_id
INNER JOIN composition c ON d.restaurant_id = c.restaurant_id AND d.dish_id = c.dish_id
GROUP BY d.restaurant_id, cuisine_id
HAVING Count(distinct(cuisine_id)) > 1

OR

SELECT 
    d.restaurant_id, 
    restaurant_category, 
    dish_name, 
    cuisine_id, 
    Count(ingredient_id)  
FROM restaurant r
INNER JOIN dish d ON r.restaurant_id = d.restaurant_id
INNER JOIN composition c ON d.restaurant_id = c.restaurant_id AND d.dish_id = c.dish_id
GROUP BY d.restaurant_id, restaurant_category, dish_name, cuisine_id, 
HAVING Count(distinct(cuisine_id)) > 1

If you need all of those columns then you will need to do this as a sub-query.

Answer 3

Were this SQL Server, I would say you have an error in your GROUP BY clause. Try:

SELECT d.restaurant_id, restaurant_category, dish_name, cuisine_id, COUNT(ingredient_id)
FROM restaurant r, dish d, composition c
WHERE r.restaurant_id = d.restaurant_id
  AND d.restaurant_id = c.restaurant_id
  AND d.dish_id = c.dish_id
GROUP BY d.restaurant_id, restaurant_category, dish_name, cuisine_id
HAVING COUNT(DISTINCT cuisine_id) > 1;