specifying multiple distinct columns in a COUNT() function.

[anuragmin]

Hi, How do I specify multiple distinct columns in a count function. I am getting an error if I give

SQL> select count(distinct company,time,region) from test;
select count(distinct company,time,region) from test
*
ERROR at line 1:
ORA-00909: invalid number of arguments

But it works if I specify only one column

1* select count(distinct (company)) from test
SQL> /

COUNT(DISTINCT(COMPANY))
------------------------
478

Please suggest.

Thanks
Anurag

[uday]

you can try

select count(distinct company||time||region) from test

See if that works

uday

[anuragmin]

It worked Uday, Thanks.

But How and Why???????????

Anurag

[pando]

because instead of grouping one column you group a group of columns and make it a single column

for example

colA----------------------------colB
1-------------------------------1
1-------------------------------1
1-------------------------------2
2-------------------------------2
3-------------------------------1
3-------------------------------2
2-------------------------------2

if you use the query uday mentioned you are actually looking the distinct value

11
12
22
21
32

hm it´s a bit hard for me to explain, hope you understand it (this is like mathematics...)

[marist89]

Or, you can try
select count(*) from (select distinct company, time, region from test) t

[jmodic]

Originally posted by marist89
Or, you can try
select count(*) from (select distinct company, time, region from test) t

YES. Much more elegant solution. Although there is one more sort operation here, there is no implicit datatype conversion which might result in better performance.

Also there is one more catch with uday's suggestion, which one must be avare of. TIME is obviously a DATE datatype. As there is no explicit conversion in uday's suggestion, Oracle performs implicit TO_CHAR conversion on date column. As default NLS_DATE_FORMAT *typicaly* contains only the date portion without the time component, the implicit date-to-char conversion will probably truncate the time portion, so you might actually get much less distinct rows as there realy are with "concatenation approach". If you go with this "distrinct concatenation" you'd better use explicit TO_CHAR date conversion with appropriate format mask.

[anuragmin]

Hi,
In my case time column has a varchar. ALso I did a test for performance by using "set timing on" and found that uday's solution is faster than marist89's.

Otherwise marist89's solution is a better one if time column is of date datatype.

Thanks
Anurag

[marist89]

Originally posted by jmodic
YES. Much more elegant solution. Although there is one more sort operation here, there is no implicit datatype conversion which might result in better performance.

I vote for COOL over FAST & EFFICIENT every time.