how to select count(distinct)?

[beginner]

select count(distinct a) from tableA
works fine.

but select count(distinct a,b) from tableA;
didn't work.

why?

[pando]

select count(distinct a||b) from tableA

distinct is for 1 column not for 2, by concatenating the two columns it becomes one so distinct works

[marist89]

It doesn't work because count() operates on one field. Here, you are specifying two fields. SELECT count(a,b) doesn't work either, so why should SELECT count(distinct a, b) work?

Maybe you are looking for SELECT count(distinct a), count(distinct b) ?

[pando]

I make a pardon, count is for 1 column not distinct


select count(distinct a||rpad(' ', 10, ' ')||b) from tableA

probably give you better reading

[jmodic]

Or maybe

SELECT COUNT(*) FROM (SELECT DISTINCT a, b FROM tableA);

which aslo takes care of possible quirks if a and b are not of character type....

[Edited by jmodic on 10-03-2001 at 04:38 PM]

[svk]

Your table XYZ. Two column I and J. Any datatype.

Define a view as follows :
create view vw_xyz as
select i,j from xyz
union
select i,j from xyz;

Then select count(*) from vw_xyz gives you the intended result of count(distinct i,j).

[jmodic]

Originally posted by svk
Your table XYZ. Two column I and J. Any datatype.

Define a view as follows :
create view vw_xyz as
select i,j from xyz
union
select i,j from xyz;

Then select count(*) from vw_xyz gives you the intended result of count(distinct i,j).

Yes, but at what expences? Two full table scans! Your view would retur exactly the same result if you change it to more simple and much more efficient query:

SELECT DISTINCT i, j FROM xyz;