CODEWITHSUNDEEP
mysqlsqlsqlite
user2296424
user2296424

Reputation:

SQLite COUNT and selecting from multiple tables

Consider the database (made up for example):

Table 'owner'
id     name
1      john
2      andrew
3      peter 

Table 'duck'
id     name     ownerID
1      duck1    1
2      duck2    1
3      duck3    1
4      duck4    2
5      duck5    2
6      duck6    3
7      duck7    3
8      duck8    1
9      duck9    3
10     duck10   3
11     duck11   1
12     duck12   2

Table 'food'
id     name     type     duckID
1      beef     meat     4
2      grass    veg      8
3      lemon    fruit    5
4      apple    fruit    3
5      pizza    snack    7

I wish to write some SQL that for each OWNER, COUNT the number of ducks which eat some kind of food. For example for owner John, there would be 2 ducks (ducks 3 and 8).

So far I am writing this code:

select owner.name, count(duck.ownerID)
from duck, food, owner
where duck.id == food.duckID
and duck.ownerID == owner.id;

I am getting the result:

Peter | 5

Any suggestions are appreciated.

Upvotes: 0

Views: 1831

Answers (1)

phlogratos
phlogratos

Reputation: 13924

This is done with an group by clause:

select owner.name, food.name, count(duck.id)
from duck, food, owner
where duck.id == food.duckID
and duck.ownerID == owner.id
group by owner.name, food.name;

This query gives you one row for each combination of owner name and food name with the number of the owner's ducks eating this food.

Upvotes: 1

Related Questions