# Thread: how to select count(distinct)?

1. Member
Join Date
Jun 2001
Posts
193
select count(distinct a) from tableA
works fine.

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

why?

2. Pando & Company
Join Date
Jun 2000
Location
Posts
7,447
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

3. 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) ?

4. Pando & Company
Join Date
Jun 2000
Location
Posts
7,447
I make a pardon, count is for 1 column not distinct

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

5. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
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]

6. svk
Join Date
Jul 2000
Posts
521
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).

7. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
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;

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•