-
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
-
you can try
select count(distinct company||time||region) from test
See if that works
uday
-
It worked Uday, Thanks.
But How and Why???????????
Anurag
-
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...)
-
Or, you can try
select count(*) from (select distinct company, time, region from test) t
Jeff Hunter
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
-
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.
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|