SQL - WHERE AGGREGATE>1

问题:

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;

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

How do I write the same query to only return firstnames that occur more than once? i.e. in the above example only return the rows for jeff and adam.

回答1:

You want the having clause, like so:

select 
    firstname,
    count(*) 
from Customers 
group by firstname
having count(*) > 1
order by 1

回答2:

group by 2 order by 1 is terrible, I should say. Use proper column names if that's supported: this will drastically improve readability.

With that in mind,

select firstname, count(*) c 
from Customers 
group by firstname  
having count(*) > 1 -- Kudos to Shannon
order by c;

回答3:

That's what the HAVING clause does. I'm not sure if this will work in informix, but give it a shot:

select firstname, count(*) 
from Customers 
group by firstname
HAVING COUNT(*) > 1