Xi Vix Xi Vix -4 years ago 56
SQL Question

How do I do a SQL query based on number of quantity

I have a table containing email addresses and account numbers (amongst other data).

I have removed the other data for simplification.

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


Per the example above, most accounts have multiple email addresses.

I need to create a query to tell me:


  • how many accounts have 1 email address

  • how many accounts have 2 email addresses

    ...

  • how many accounts have 10 email addresses


Answer Source

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

SELECT q.email_counter, COUNT(*) AS num_accounts
    FROM (SELECT account_number, 
                 COUNT(DISTINCT email_address) AS email_counter
              FROM YourTable
              GROUP BY account_number) q
    GROUP BY q.email_counter;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download