SQL - Counting WHERE AGGREGATE> 1

Imagine I have a db table for clients containing {id, username, firstname, lastname}

If I want to find how many instances have different names, I can do:

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

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

      

How to count the number of first names 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;

      

+2


source to share


3 answers


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 occurred more than once. Then you count all these names. The offer HAVING

allows you to filter by aggregates. It's like a suggestion WHERE

, except that you can use aggregate functions.

+5


source


No need for a subquery.

Try:

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

      



Or, order the most featured name:

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

      

+2


source


This would do it:

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

      

+1


source







All Articles