2

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.

Johannes Kuhn
  • 14,778
  • 4
  • 49
  • 73
Andy Longwill
  • 624
  • 9
  • 25
  • Remember _HAVING_ filters at the group level, see: http://stackoverflow.com/questions/1130062/what-is-the-execution-sequence-of-group-by-having-and-where-clause-in-sql-server/1131076#1131076 – KM. Jul 31 '09 at 15:15

3 Answers3

10

You want the having clause, like so:

select 
    firstname,
    count(*) 
from Customers 
group by firstname
having count(*) > 1
order by 1
Eric
  • 92,005
  • 12
  • 114
  • 115
4

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;
Anton Gogolev
  • 113,561
  • 39
  • 200
  • 288
  • 2
    I don't know about Informix, but generally the column aliases from the select clause won't be available in the having clause, since having is evaluated earlier. – Shannon Severance Jul 31 '09 at 15:13
  • @Shannon: SQL never ceases to amaze me with its inconsistency. Thanks! – Anton Gogolev Jul 31 '09 at 15:26
  • +1 for 'group by 2 order by 1 is terrible'. However, it should also have been an error; at least, the server should have said "must group by firstname (non-aggregate in select-list) too". – Jonathan Leffler Jul 31 '09 at 22:51
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
Aaron Alton
  • 22,728
  • 6
  • 34
  • 32