0

Here is my table structure

Food Type Table

    food_type_id, food_type_name, food_id 

Food Table

    food_id, food_name, food_calorie

Here is my query that displays the food type such that there is greater than 10 food within the food type that have calorie greater than 1000

SELECT ftt.food_type_name
FROM food_type_table as ftt
JOIN food_table as ft ON ft.food_id = ftt.food_id
WHERE ft.food_calorie > 1000
GROUP BY ftt.food_type_id
HAVING COUNT(ft.food_id) > 10

But if I want to display the actual count of the food per food type along with the food name

SELECT ftt.food_type_name, ft.food_name, COUNT(ft.food_id)
FROM food_type_table as ftt
JOIN food_table as ft ON ft.food_id = ftt.food_id
WHERE ft.food_calorie > 1000
GROUP BY ftt.food_type_id
HAVING COUNT(ft.food_id) > 10

the above will display a food type and one random food (that satisfy calorie constraint) per food type and the count of the food

eg.

chip, doritos, 2
pizza, papa_john, 2

instead of 

chip, doritos, 2
chip, lays, 2
pizza, papa_john, 2
pizza, pizza_hut, 2

Is it possible to display both the aggregate number and the query result without subquery?

user445670
  • 179
  • 8
  • Your schema is confusing. If each food_type can have multiple food entries, then the food_type_id should be the FK in food_table but you have food_id in food_type_table – user3885927 Nov 01 '16 at 18:09
  • You can't do this without some sort of subquery. See this http://stackoverflow.com/a/5920259/3885927 – user3885927 Nov 01 '16 at 18:20

0 Answers0