Re: SQL: count distinct multi-column
I sometimes use a view to perform the intermediate step.
CREATE VIEW tview (k1, k2, k3) AS
SELECT DISTINCT k1, k2, k3 FROM tbl;
SELECT COUNT(*) FROM tview;
--------------------------------------------------------------
Chuck Ludwigsen <cludwigs@hotmail.com>
Database Engineer
President - Memphis Informix User's Group <miug@hotmail.com>
--------------------------------------------------------------
Roger W. Fischer wrote:
>
> Greetings:
>
> In SQL, is there a way to count the number of rows that have distinct
> multi-column keys? It seems that the count function does not accept a
> distinct expression with multiple columns.
>
> Table:
> Column Type Null
> k1 char(3) no
> k2 char(8) no
> k3 char(11) no
> ref1 char(20) no
> ...
>
> SQL:
> SELECT COUNT( DISTINCT k1,k2,k3) FROM tbl;>
> This query failed with an error at the comma following k1.
>
> I also tried the following to no avail:
> SELECT COUNT( DISTINCT k1||k2||k3) FROM tbl;>
> Note that the query without the count works OK. Also there are too many
> rows to use "GROUP BY k1,k2,k3".
>
> Is there any other way to get the count of unique keys?
>
> Please reply through email, as the retention period on our site is very
> short. Thanks...
>
> Roger
> --
> Roger W. Fischer roger_fischer@nortel-nsm.com
> Nortel NST roger.fischer@bnr.ca
> Vancouver, BC (Canada) fischer@ncrsu123.crest.nt.com