SQL - WHERE AGGREGATE>1

Question

Hi,

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.

Thanks

Andrew

Answer 1

You want the having clause, like so:

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

Answer 2

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

Answer 3

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;