Re: Having problem with distinct and count
In a discussion of COUNT(DISTINCT) over several columns or an expression, bozon showed a workaround using a view that concatenates name and number, then counts distinct on the view column. David noted that IDS 10 allows expressions in the distinct part, but only one value. Bozon said other databases support multiple columns and cited a SQL book. No further solution was given.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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.