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.

Answers


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.


Need Your Help

Marionette: unexpected Missing itemViewContainer

model-view-controller marionette composite

I'm getting a 'Missing itemViewContainer' error that I wasn't expecting. My module looks like this:

table needs indexes to improve performance

sql-server performance indexing clustered-index non-clustered-index

I was having timeout issue when giving long period of DateTime in below query (query runs from c# application). Table had 30 million rows with a non-clustered index on ID(not a primary key).