Counting twice in a request, once using constraints
Considering 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 the number of children in a class, I would do the following:
SELECT class, COUNT(child) FROM Table1 GROUP BY class
and if I wanted to query the number of children in a class with glasses I would do the following:
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 I really want:
SELECT class, COUNT(child), COUNT(child wearing glasses)
and to be honest I have no idea how to do this in just one request.
help?
a source to share
You can try something like this (no 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
A bit of a hack and it might be easier for you to do it in two requests, but this method worked for me as a last resort
a source to share
This is not a perfect solution, it is just an 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
Output:
a b
art 1
math 3
1 art
2 math
In this we can get some idea for developing more things.
a source to share
Possibly the post is 5 years old, but I also solved it.
Working with numbers like 1 and 0 for yes / no and true / false will be easier for you.
The solution would be (if you change yes and no to 1 and 0):
SELECT class,
COUNT(distinct Table1.child) AS NumbofChilds,
SUM(Table2.glasses) AS ChildswithGlasses
FROM Table1 INNER JOIN Table2 ON Table1.child=Table2.child
GROUP BY class;
The syntax should be checked, but I have a similar query for my tables and it works.
a source to share