Home » SQL & PL/SQL » SQL & PL/SQL » Count distinct values in column for >2 tables
Count distinct values in column for >2 tables [message #270263] Wed, 26 September 2007 06:17 Go to next message
ram_ocp
Messages: 49
Registered: December 2005
Location: Karnataka
Member

Hi,

I have some 12 tables in my oracle database having a common column say 'type'.I would like to count the distinct values in the column 'type' for all the 12 tables at one time.


I tried using left outer join for all 12 tables and also dba_tab_cols but didnt help.

Can you let me know if there is any way out

Thanks
Ram
Re: Count distinct values in column for >2 tables [message #270267 is a reply to message #270263] Wed, 26 September 2007 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a "UNION ALL" between all tables and don't use TYPE as column name, it is a reserved word.

Regards
Michel
Re: Count distinct values in column for >2 tables [message #270276 is a reply to message #270263] Wed, 26 September 2007 06:42 Go to previous messageGo to next message
ram_ocp
Messages: 49
Registered: December 2005
Location: Karnataka
Member
Hi,

Thanks so much -i now have the list of all the values.But the problem is it is also showing duplicate values though i am using DISTINCT keyword. Also it is not accepting the count function

SQL> select name from arash union all select name from brash union all select na
me from crash ;

NA
--
aa
aa
bb
bb
cc
cc
dd
ee
cc
bb
aa
bs
bg

a
b

c
as
bs
ds

21 rows selected.

SQL> select distinct name from arash union all select name from brash union all select name from crash ;

NA
--
aa
bb
dd
ee
cc
cc
bb
aa
bs
bg

a
b

c
as
bs
ds

18 rows selected.

SQL> select count(name from arash union all select name from brash union all sel
ect name from crash) ;
select count(name from arash union all select name from brash union all select n
ame from crash)
*
ERROR at line 1:
ORA-00907: missing right parenthesis


Pls suggest the workaround

Thanks
Ram
Re: Count distinct values in column for >2 tables [message #270277 is a reply to message #270276] Wed, 26 September 2007 06:47 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Use count on top of the union's.
select count(*)
  from(select name from arash
        union 
       select name from brash
        union 
       select name from crash);
By
Vamsi
Re: Count distinct values in column for >2 tables [message #270281 is a reply to message #270276] Wed, 26 September 2007 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

select distinct whatIwant
from (select whatIwant from tab1 union all ...)


Regards
Michel
Re: Count distinct values in column for >2 tables [message #270286 is a reply to message #270263] Wed, 26 September 2007 07:01 Go to previous message
ram_ocp
Messages: 49
Registered: December 2005
Location: Karnataka
Member

Your query gives the result i wanted.Thanks very much !!
Previous Topic: select where is null
Next Topic: How identity gets maintained?
Goto Forum:
  


Current Time: Sun Dec 04 04:39:17 CST 2016

Total time taken to generate the page: 0.04713 seconds