Re: count distinct

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 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?) -- 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/ ---