Getting the number of rows returned by a select with group by.

I've have a quick look around and don't seem to be able to find a solution to count the number of rows returned by queries like:- SELECT a,b,c,COUNT(*) FROM x GROUP BY a,b,c HAVING COUNT(*) > 1 I've seen that syntax like:- SELECT COUNT(*) FROM (SELECT a,b,c,COUNT(*) FROM x GROUP BY a,b,c HAVINGCOUNT(*) > 1) x1 works on other databases. Is there a simple way to do this on Informix without using a temporary table? Target databases will be IDS version 7.30 or above. Thanks in advance, David sending to informix-list

David N. Heydon wrote: > I've have a quick look around and don't seem to be able to find a > solution to count the number of rows returned by queries like:- > > SELECT a,b,c,COUNT(*) FROM x > GROUP BY a,b,c > HAVING COUNT(*) > 1> > I've seen that syntax like:- > > SELECT COUNT(*) FROM (SELECT a,b,c,COUNT(*) FROM x GROUP BY a,b,c > HAVING COUNT(*) > 1) x1> > works on other databases. Is there a simple way to do this on > Informix without using a temporary table? That notation does not yet work in IDS -- it's high on my hitlist, but not there yet. > Target databases will be IDS version 7.30 or above. I trust you mean 7.31 or later -- you should not be using 7.30 any more; nor, indeed, should you be using anything less than 7.31.xD7 (released in January 2004). Anything earlier is not good news. Do you mean that the solution must work in 7.31 and with 9.x, or is a solution that works only in 9.x permitted? SELECT COUNT(*) FROM TABLE(MULTISET( SELECT a,b,c,COUNT(*) FROM x GROUP BY a,b,c HAVING COUNT(*) > 1 )) This won't work in 7.31. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> "I don't suffer from insanity - I enjoy every minute of it"