SQLite COUNT and selecting from multiple tables

Question

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.

phlogratos · Accepted Answer · 2013-11-17 13:27:21Z

1

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.

answered Nov 17, 2013 at 13:27

phlogratos

14k1 gold badge35 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2296424 Over a year ago

blessings for sharing your wisdom

Collectives™ on Stack Overflow

SQLite COUNT and selecting from multiple tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related