Anthony Anthony - 1 month ago 5
MySQL Question

How to join two tables to get the count on a where query

I have two tables

Table 1:

ppl


Cust No.| sex
--------| -------
10 | M
13 | F
12 | M
19 | F
20 | M
22 | M
34 | F


Table 2:
hobbies


Cust No. | Hobby
---------| ---------
10 | Movies
10 | Hiking
10 | Programming
12 | Biking
12 | Movies
12 | Reading
19 | Biking
20 | Reading
22 | Movies
34 | Flying
22 | Biking


I am trying to find a distribution of most popular hobbies by Male / Female in
DESC
order from the table tables.

I have written the query to find the most popular hobbies in
DESC
order

select Hobby, count(*)
from hobbies
group by Hobby


Question

How can I join this with
ppl
table and then get a distribution by M/F for each one?

Note that I would like the most popular hobbies to be in DESC order. First I need most popular hobbies and then the breakdown of them in M/F

The result based on the above set up would be:

Hobby | M | F
-------------| -----|----
Movies | 3 | 0
Hiking | 1 | 0
Programing | 1 | 0
Biking | 2 | 1
Reading | 2 | 0
Flying | 0 | 1

Answer

This is a conditional aggregation query with a join:

select h.Hobby,
       sum(case when gender = 'F' then 1 else 0 end) as females,
       sum(case when gender = 'M' then 1 else 0 end) as males
from hobbies h join
     people p
     on h.custno = p.custno
group by h.Hobby
order by count(*) desc;
Comments