[rydenius]

Not all databases support COUNT(DISTINCT list) where 'list' is a comma separated list of more than one columns. For example DB2 and PGSQL does not (at least in the versions I've seen). Does anybody know a standard way to write a portable version of that expression, that works with most db's?

What I want to do:

SELECT COUNT(DISTINCT a, b) FROM c GROUP BY d;

...but without using multiple column arguments to COUNT DISTINCT.

Any ideas?

[andrewst]

Originally posted by rydenius
Not all databases support COUNT(DISTINCT list) where 'list' is a comma separated list of more than one columns. For example DB2 and PGSQL does not (at least in the versions I've seen). Does anybody know a standard way to write a portable version of that expression, that works with most db's?

What I want to do:

SELECT COUNT(DISTINCT a, b) FROM c GROUP BY d;

...but without using multiple column arguments to COUNT DISTINCT.

Any ideas?

Oracle doesn't support that either. What one typically does, which I imagine would work on other DBMSs (but can't be sure) is:

SELECT COUNT(DISTINCT a||b) FROM c GROUP BY d;

If there are numbers and/or dates, these need to be converted to character first, which is where I imagine you will become DBMS-dependent again with date format masks etc.

[rydenius]

Thanks a lot! But then I have another problem instead:

COUNT(DISTINCT ...) should exclude rows with NULL values in column a or b caused by for example OUTER JOINS. Using the techique with '||' the NULL rows will be counted.

In my case the columns a and b are both integers, so I tried something like

a || '.' || b

Do you have a smart solution for that too?

[andrewst]

Originally posted by rydenius
Thanks a lot! But then I have another problem instead:

COUNT(DISTINCT ...) should exclude rows with NULL values in column a or b caused by for example OUTER JOINS. Using the techique with '||' the NULL rows will be counted.

In my case the columns a and b are both integers, so I tried something like

a || '.' || b

Do you have a smart solution for that too?

Why not just add a WHERE clause:

WHERE a IS NOT NULL
AND b IS NOT NULL
...

[rydenius]

Sure. Sometimes one just gets blind...

Thanks!

[lrobin3]

what about something like this:

select count(*)
from ( select a, b, count(*) from Table group by a, b )

Not as pretty, but with a little massaging, that should work in most DBMS implementations....either as a dynamic view or as a correlated sub-query in the select line.

Granted, efficiency is a little lacking.......

Layne