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

jQuery Validate plugin submitHandler delaying form submit

jquery forms post jquery-validate

I am using jQuery Validate plugin's submitHandler to disable Submit Button, but once form submitted button is disabled and I have to wait like good 5 - 10 seconds before form will be POSTED to other

Interior color gives error in vba?

excel vba

I am getting a runtime error, 1004 when I run the below code in vba

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.