• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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.
0
rishi4you
Asked:
rishi4you
  • 3
  • 3
  • 2
  • +6
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
  • 2
  • +6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now