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 to share
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 to share