How to retrieve counts of mutiple columns across mutiple tables in Oracle

I have a requirement wherein i need to generate a report which shows the count of various columns across mutiple tables, Some of the tables do have a parent- child relationship and i can form a join. but some are not related at all. Is there a way to find this in a single query.
rishi4youAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rishi4youAuthor Commented:
??
0
SharathData EngineerCommented:
What do you mean by count of column? Do you want the  number of columns in a table?
If you are referring about columns, then how does parent-child relationship related here?
To get the number of columns in a table, you can try like this.
select count(*) from all_tab_columns where table_name = 'your_table_name';
0
mohsajdiCommented:
SELECT TNAME,COUNT(CNAME)
FROM col
GROUP BY TNAME
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Naveen KumarProduction Manager / Application Support ManagerCommented:
may be something like the below...

if it is join, then you can do something like the below....

select count(a.empnname), count(a.empno), count(b.deptname)
from emp a, dept b
where a.deptno = b.deptno

if the tables are not related, something like the below....

select count(terminated_date), count(join_date) , ( select count(active_date) from hired_emps ) active_count
from emp_hire_table
0
mohsajdiCommented:
you need the ER (Entity Relationship) diagram
then use TOAD tool by quest software,

Good tool for ORACLE

Regards
Sajid
0
slightwv (䄆 Netminder) Commented:
If I understand what you are wanting to do, you can but I'm not sure it is the most efficient way:

select
   (select count(*) from all_objects) all_objects_count,
   (select count(*) from dual) dual_count
from dual
/

I might look at a stored procedure or function where you have more control and might be able to reuse/tweak things to be more efficient.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
K_from_OCommented:
The challenge is that if you join the tables, then your results will only indicate the content of the joined tables that satisfies the join condition.

That is, if you have two tables Table_A and Table_B.  Each has a numeric key upon which the tables are joined.

Table_A contains key values 1, 2, 3, and Table_B contains 1, 3, 5.  When you join them, if you use equijoin you get 2 rows.  If you outer join the tables, you'll get 3 rows.  Without knowing exactly what results you seek, the answer is vague.

If some tables are unrelated, and that doesn't affect your results, then just query each table independently and put these queries together:

select (select count(1) from table_A) table_a_count, (select count(1) from Table_B) table_B_count
from dual;

0
awking00Commented:
Can you post some sample data with the relevant table structures and columns and what you expect the output to be?
0
dka07Commented:
Do you mean a count of column? Mostly people want a count of rows or records. Why columns?
0
rishi4youAuthor Commented:
I meant count of rows for different columns some within the same table and some in other tables
0
SharathData EngineerCommented:
>> I meant count of rows for different columns some within the same table and some in other tables
Count of rows will be same for all the columns. In fact count of rows is for a table. Now can you explain what exactly you want?
Count of records for all the tables?
0
rishi4youAuthor Commented:
Count of items(valid values excluding null) in different columns of one or more tables
0
SharathData EngineerCommented:
Do you mean this?
As you mentioned across all the table, what do you mean by that? What would be your final expected result?

SELECT Count(column_1) AS cnt_column1, 
       Count(column_2) AS cnt_column2, 
       Count(column_3) AS cnt_column3 
FROM   your_table

Open in new window

0
slightwv (䄆 Netminder) Commented:
You never commented on:  http:#28562095  Is that not what you are after?
0
DavidSenior Oracle Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.