counting twice in a query, once using restrictions
- by Andrew Heath
Given the following tables:
Table1
[class] [child]
math boy1
math boy2
math boy3
art boy1
Table2
[child] [glasses]
boy1 yes
boy2 yes
boy3 no
If I want to query for number of children per class, I'd do this:
SELECT class, COUNT(child) FROM Table1 GROUP BY class
and if I wanted to query for number of children per class wearing glasses, I'd do this:
SELECT Table1.class, COUNT(table1.child) FROM Table1
LEFT JOIN Table2 ON Table1.child=Table2.child
WHERE Table2.glasses='yes' GROUP BY Table1.class
but what I really want to do is:
SELECT class, COUNT(child), COUNT(child wearing glasses)
and frankly I have no idea how to do that in only one query.
help?
