SQL: How to COUNT the number of keys that appear in a certain number of rows

I have the following two SQL tables dog and toy:

dog

id	gender
1	2
2	2
3	2
4	1

toy

toy_id	dog_id
1	1
1	2
2	1
2	2

We want to find out how many female dogs have a certain 0 toys, 1 toy, 2 toys… A value of 2 in the gender column indicates the dog is female.

The answer should be as seen below because dogs 1 and 2 are both female and have 2 toys, where as dog 3 is female and has 0 toys:

num_toys	num_dogs
0	1
2	2

What SQL query will get me to this answer? The query below gives me a table with all the female dogs and their toys. How do I expand on this to aggregate?

Select toys.toy_id, toys.dog_id
From toys
Where toys.toy_id IN (
    SELECT dog.id
    From dog
    Where dog.gender = 2
    )

Answer

Hmmm . . . You could use two levels of aggregation:

select num_toys, count(*)
from (select d.id, count(t.dog_id) as num_toys
      from dogs d left join
           toys t
           on t.dog_id = d.id
      where d.gender = 2
      group by d.id
     ) d
group by num_toys;

The trick here is to use a let join in the subquery, so you can get dogs with no toys.