Home » SQL & PL/SQL » SQL & PL/SQL » combination of count and distinct query
combination of count and distinct query [message #235578] Mon, 07 May 2007 05:05 Go to next message
adroit.ramesh
Messages: 14
Registered: November 2005
Junior Member
Hi,

I am trying to find the count of distinct of two columns.Is it possible?

For ex:

Select count(distinct empno,deptno) from emp


Regards
Ramesh
Re: combination of count and distinct query [message #235580 is a reply to message #235578] Mon, 07 May 2007 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select count(*) from (select distinct empno, deptno from em);

Regards
Michel
Re: combination of count and distinct query [message #235581 is a reply to message #235578] Mon, 07 May 2007 05:10 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
either
Select count(*) from (Select distinct empno,deptno from emp) 


or assuming both are number field

Select Count(distinct to_char(empno)||to_char(deptno)) from emp 



Re: combination of count and distinct query [message #235583 is a reply to message #235578] Mon, 07 May 2007 05:11 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
oops sorry michael, I did not see you already posted reply.
Re: combination of count and distinct query [message #235584 is a reply to message #235583] Mon, 07 May 2007 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Given there are only 2 minutes between our posts, I shouldn't think you wanted to double post mine.

Just a small remark for your second solution (which is often a useful one when embedded in complex query), you have to separate the 2 fields by a separator a use a fixed format to prevent for false equality.

For instance, '1'||'23' will be count as a duplicate of '12'||'3'.
Either use
to_char(empno)||'/'||to_char(deptno)
or
to_char(empno,'00000000000')||to_char(deptno,'00000000000')
(for instance)

Regards
Michel

Re: combination of count and distinct query [message #235586 is a reply to message #235584] Mon, 07 May 2007 05:25 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
yes agreed, second solutions need to have field separator. thanks for pointing out
icon9.gif  Re: combination of count and distinct query [message #235590 is a reply to message #235583] Mon, 07 May 2007 05:51 Go to previous message
adroit.ramesh
Messages: 14
Registered: November 2005
Junior Member
Got it.Thanks a lot.
Previous Topic: how to use a cursor so as it selects value from another cursor
Next Topic: MAXEXTENTS in Index
Goto Forum:
  


Current Time: Mon Dec 05 12:54:09 CST 2016

Total time taken to generate the page: 0.06500 seconds