Re: Having problem with distinct and count

create view stupid_results_view(sTupid) as select name || number fromresults ; select * from stupid_results_view ; select count(distinct sTupid) from stupid_results_view ; stupid test11 stupid test11 stupid test11 stupid test22 stupid test22 stupid test22 (count) 2 Table and column names reflect my opinion of the way this is implemented in Informix. I am pretty sure other databases allow calculations in the count() function or multiple columns but you have to work around a syntax limitation in informix. I don't think this will be really fast unless you use functional indexes and informix can figure out that is what you are trying to do. PS. I am almost certainly not handling nulls the way that you would want.

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls1047.htm You can include expressions in the distinct part of the query. But it has to be one value that you do the distinct on. Do I see another feature request for my list?

Yes, I am pretty sure this is implemented in other databases and would be handy. I just checked my SQL in a nutshell book and it has the following entry about count distinct: count distinct counts the occurences of all non-NULL values in the specified column(s). The paren "s" clenches it. ;-) someone must allow multiple columns.

Heck, Yeah. I know this is a feature in other databases. A quick check of my book "SQL in a Nutshell" gives the following definition: "COUNT DISTINCT counts the occurences of all unique, non-null values in the specified column(s)" The paren "s" is a clear indication that you can use 1 or more columns.