select count(distinct a) from tableA
works fine.
but select count(distinct a,b) from tableA;
didn't work.
why?
Printable View
select count(distinct a) from tableA
works fine.
but select count(distinct a,b) from tableA;
didn't work.
why?
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
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) ?
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
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]
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:Quote:
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).
SELECT DISTINCT i, j FROM xyz;