SQL - counting WHERE AGGREGATE>1

Question

Imagine I have a db table of Customers containing {id,username,firstname,lastname}

If I want to find how many instances there are of different firstnames I can do:

select firstname, count(*) from Customers group by 2 order by 1;

   firstname | count(*)
   ====================
   bob       |   1
   jeff      |   2
   adam      |   5

How do I count the number of firstnames that occur more than once? In pseudo-sql it would be something like:

select
    COUNT(
        firstname,
        count(*) as num_occurrences
    )
from 
    Customers 
group by 2 
having num_occurrences > 1;

Answer 1

This would do it:

select count(username)
  from (select username
          from Customers
         group by username
        having count(*) > 1);

Answer 2

You have the right idea:

SELECT COUNT(*)
FROM (
  SELECT firstname
  FROM Customers
  GROUP BY firstname
  HAVING COUNT(*) >= 2
)

The subquery counts the first names that have occurred more than once. Then you count all of those first names. The HAVING clause allows you to filter by aggregates. It's like a WHERE clause, except you can use aggregate functions.

Answer 3

There is no need for a subquery.

Try:

SELECT firstname, COUNT(*)
  FROM Customers
 GROUP BY firstname
HAVING COUNT(*) > 1
 ORDER BY firstname

Or, order by the most represented name:

SELECT firstname, COUNT(*) AS custcount
  FROM Customers
 GROUP BY firstname
HAVING COUNT(*) > 1
 ORDER BY custcount DESC;