Re: Problems with "COUNT(DISTINCT xxx)" and end-user Reporting Tools

On 22 Aug 1995, Jeff Webster wrote: > Couple of ?s for those of you who understand this stuff. I've been evaluating > several end-user reporting tools (e.g. IQ Pro, BusinessObjects, Impromptu), and > they all use the following syntax for the COUNT aggregate function: > "COUNT(xxx)" > whereas Informix SQL requires: > "COUNT(DISTINCT xxx)" > which is causing me all kinds of grief. > **** stuff deleted **** > Is the "COUNT DISTINCT" keyword unique to Informix? **** stuff deleted **** Jeff, count (unique xxx) is also possible, but try count(*). It's a lot faster, and works just fine if you're counting records in a normalized table, where the field you want is indeed unique. Otherwise, using group by and count together is probably faster, e.g. select your_field,count (*) from tablex group by your_field; Informix SQL is pretty vanilla, and yes there are probably other products that offer non-standard extensions. I suppose the count (xxx) syntax in the other products is taken to mean count all unique occurrences of xxx by default. Yours, Nick ********************************* Nick Nobbe, Library of Congress NLS/BPH mail: nnob@loc.gov *********************************