counting twice in a query, once using restrictions

Question

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?

Answer 1

You can try something like this (not syntax checked):

SELECT 
   class, 
   COUNT(distinct Table1.child), 
   SUM(IF(Table2.glasses='yes', 1, 0))
FROM Table1
LEFT JOIN Table2 ON Table1.child=Table2.child

It's a bit hacky and you may find it's just easier to do it in two queries but this technique has worked for me in a pinch

Answer 2

in MySQL you can do it something like this:

SELECT class, COUNT(child), SUM(IF(glasses = 'yes', 1, 0)

Answer 3

It is not a perfect solution, it is just a alternative solution,

SELECT class AS a, COUNT( child ) AS b
FROM Table1
GROUP BY class
UNION ALL SELECT COUNT( table1.child ) AS a, Table1.class AS b
FROM Table1
LEFT JOIN Table2 ON Table1.child = Table2.child
WHERE Table2.glasses = 'yes'
GROUP BY Table1.class

The output is :

a   b
art     1
math    3
1   art
2   math

In this, we may got some idea for develop more thing.