Home » SQL & PL/SQL » SQL & PL/SQL » SELECT Count question
SELECT Count question [message #206083] Tue, 28 November 2006 11:12 Go to next message
brianhevans
Messages: 2
Registered: November 2006
Junior Member
Hello all, this is my first post. I look forward to collaborating with any of you in the future. Today I have a question about aggregate functions in the SELECT list of a query.
The query is pasted below. What I am trying to do is print a list of records for each table, the owner of each table, the table name and the number of columns in each of those tables.
Everything works fine except for the first column in the SELECT list which actually produces the same result as the last item in the SELECT list.

select count(*) as Record_Count, at.owner as Owner, at.table_name as Table_Name, count(atc.column_name)
from all_tables at
inner join all_tab_columns atc
on at.table_name = atc.table_name
where at.owner not in ('SYS', 'SYSTEM')
group by at.owner, at.table_name
order by at.owner, at.table_name, count(atc.column_name);

Any help is most appreciated!
Re: SELECT Count question [message #206122 is a reply to message #206083] Tue, 28 November 2006 19:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Firstly, you need to include OWNER in your join clause.

What do you want Record_Count to show if not the number of columns? COUNT(*) displays the number of rows in the group, and COUNT(col) displays the number of non-null values of col in the group. COUNT(DISTINCT col) counts the number of DISTINCT non-null values of col in the group.

By my reckoning, all of these should display the same value for your query. ie. the number of columns in the table.

Ross Leishman
Re: SELECT Count question [message #206538 is a reply to message #206122] Thu, 30 November 2006 08:17 Go to previous messageGo to next message
brianhevans
Messages: 2
Registered: November 2006
Junior Member
Thanks for the help but my problem is still not solved. Not sure if I explained my question properly so I'll try again. I would like to have a record count for each table that my query returns.

Here is the query again...

select at.owner, at.table_name, count(atc.column_name)
from all_tables at, all_tab_columns atc
where at.table_name = atc.table_name
group by at.owner, at.table_name
order by at.owner, at.table_name;

The table 'all_tables' has many tables in it, but each table within 'all_tables' has several rows within each of them. I want to get the record count for each table in the 'all_tables' table. Is this a little clearer? I think I need a subquery that will fire for each table but I am not sure how to pass the table for each row returned to the subquery...can anyone help?
Re: SELECT Count question [message #206539 is a reply to message #206538] Thu, 30 November 2006 08:26 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
I'm not sure that's explained any better, but I think maybe you want to add the additional at.owner=atc.owner condition.
Re: SELECT Count question [message #206553 is a reply to message #206538] Thu, 30 November 2006 08:59 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
brianhevans wrote on Thu, 30 November 2006 08:17
Thanks for the help but my problem is still not solved. Not sure if I explained my question properly so I'll try again. I would like to have a record count for each table that my query returns.

Here is the query again...

select at.owner, at.table_name, count(atc.column_name)
from all_tables at, all_tab_columns atc
where at.table_name = atc.table_name
group by at.owner, at.table_name
order by at.owner, at.table_name;

The table 'all_tables' has many tables in it, but each table within 'all_tables' has several rows within each of them. I want to get the record count for each table in the 'all_tables' table. Is this a little clearer? I think I need a subquery that will fire for each table but I am not sure how to pass the table for each row returned to the subquery...can anyone help?


The actual number of records in the tables that are listed in all_tables is not stored in all_tables, an approximation is stored if statistics have been gathered lately. Rewrite your query as below. If you need the exact count then you will have to use a procedure and use execute immediate to run a count select on every table.

select at.owner, at.table_name,at.tot_row total_rows, count(atc.column_name) total_columns
from all_tables at, all_tab_columns atc
where at.table_name = atc.table_name
and at.owner = atc.owner
group by at.owner, at.table_name
order by at.owner, at.table_name;

[Updated on: Thu, 30 November 2006 08:59]

Report message to a moderator

Previous Topic: ORA-24801: illegal parameter value in OCI lob function
Next Topic: Auto Number or unique identifier
Goto Forum:
  


Current Time: Wed Dec 07 08:35:31 CST 2016

Total time taken to generate the page: 0.19830 seconds