Using Where and Count on two tables

Question

Using Where and Count on two tables

I have two tables

Foods
----------
ID    Name
----------
1. Chips
2. Pizza
3. Fish
4. Pasta

People
-----------------
ID Name    FoodID
-----------------
1. Robert       3
2. Norman       2
3. Leonard      4
4. Huey         3

What I have to do is get any food that belongs to more than one person. Now I have to do it with COUNT and WHERE. You might need to do an INNER JOIN.

It should be easy, but I just can't see it.

+3

mysql count where

VanZan May 15 '15 at 17:16

source to share

3 answers

select f.name, p.name, count(f.id) as total from foods f, people p 
where (f.id = p.FoodID)
group by f.name
having total > 1
order by f.name

http://sqlfiddle.com/#!9/b2a63/10

+1

johnny May 15 '15 at 17:44

source to share

select * from food inner join people  on people.id = food.id group by people.name

-1

Marcos Vinicius de Souza Gouve May 15 '15 at 17:24

source to share

Joshua R. · Accepted Answer · 2015-05-15T18:01:08+0000

You want to join tables in a common field. WHERE serves as an internal connection. Then GROUP BY Foods.name concatenates the rows by food type. Count is an aggregated operator that always works with GROUP BY.

SELECT Foods.Name, Count(*)
FROM Foods, People
WHERE Foods.ID = People.Food
GROUP BY Foods.Name
HAVING COUNT(*)>1;

Omit the first counter (*) if you only want a list of products.

Using Where and Count on two tables

More articles: