CODEWITHSUNDEEP
mysqlsql
Anthony
Anthony

Reputation: 35928

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

Upvotes: 1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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;

Upvotes: 4

Related Questions