group by - How do I do a SQL query based on number of quantity -

i have table containing email addresses , account numbers (amongst other data).

i have removed other data simplification.

123456, joe@place.com 123457, phil@place.com 123456, jil@place.com 123456, jane@place.com 123458, john@place.com 

per example above, accounts have multiple email addresses.

i need create query tell me:

• how many accounts have 1 email address
• how many accounts have 2 email addresses
  ...
• how many accounts have 10 email addresses

the inner query (q) count how many distinct email addresses each account has. outer query count how many accounts fall each counting bucket (1, 2, 3, ...).

select q.email_counter, count(*) num_accounts     (select account_number,                   count(distinct email_address) email_counter               yourtable               group account_number) q     group q.email_counter;