Re: count distinct

Peter Lancashire <Peter.Lancashire.PL1@bayer.co.uk> writes: > Ilia Perminov wrote: > > > > Hi all, > > > > Could anybody explain my why I can't use more the one > > "count (distinct ...)" in a query? For example, > > > > select count (distinct a), count (b) from a_table> > > > is a correct query, but > > > > select count (distinct a), count (distinct b) from a_table> > > > is not a correct one (Informix: 201: A syntax error has > > occurred.). I have not found anything about this restriction in > > Informix Guides. > > > > Ilia > > Copying text from the SQL Syntax guide 7.2 ... > --- > Allowing Duplicates > > You can apply the ALL, UNIQUE, or DISTINCT keywords to indicate whether > duplicate values are returned, if any exist. If you do not specify any > keywords, all the rows are returned by default. > > For example, the following query lists the stock_num and manu_code of > all items that have been ordered, excluding duplicate items: > > SELECT DISTINCT stock_num, manu_code FROM items> > You can use the DISTINCT or UNIQUE keywords once in each level of a > query or subquery. For example, the following query uses DISTINCT in > both the query and the subquery: > > SELECT DISTINCT stock_num, manu_code FROM items > WHERE order_num = (SELECT DISTINCT order_num FROM orders > WHERE customer_num = 120) > --- This part of "SQL Syntax guide 7.2" does not describe aggregate functions, it describes "SELECT DISTINCT". COUNT (DISTINCT ...) is just an aggregate function and I don't see any difference between COUNT and MAX or SUM. > This seems clear enough to me, although I confess I was caught out by > the same problem in another guise. > > The reason is that SQL deals in rows, whole rows and nothing but rows. > Two distincts could mean that the rows have to be broken up into two > disjoint sets. (I think I know what I mean, can anyone else explain it > more clearly?) I don't understand why two different functions can't be calculated for one set of rows. BTW, Oracle allows to have several COUNT(DISTINCT ...) in a query. > -- > Peter Lancashire > Information Systems Specialist, Bayer plc > Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK > Tel: +44-1635-562258, Fax: +44-1635-562281 > --- > If all else fails, read the instructions and the release notes. > Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/ > --- Ilia.