counting the number of records from multiple tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: counting the number of records from multiple tables

  1. #1
    Join Date
    Nov 2006
    Posts
    8

    counting the number of records from multiple tables

    i have nearly 10 to 15 tables of names like %price1%

    i want an output of the form

    tablename count(*)
    ---------------------
    price1 200
    price2 335
    price3 634
    ...
    ...
    ..

    could someone help me to phrase a query.


  2. #2
    Join Date
    Nov 2006
    Posts
    8

    Any replies to this please ....

    Any replies to this please ....

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    select 'price1' table_name, count(*) num_rows from price1
    union all
    select 'price2' table_name, count(*) num_rows from price2
    union all
    select 'price3' table_name, count(*) num_rows from price3
    union all
    ...
    order by 1
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2006
    Posts
    8
    Hi Dave,

    thanks for the reply, but in this case i want the table names to be framed up automatically by oracle, as there might be many tables also at times.

    any other alternates much appreciated.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Quote Originally Posted by perot
    Hi Dave,

    thanks for the reply, but in this case i want the table names to be framed up automatically by oracle, as there might be many tables also at times.

    any other alternates much appreciated.
    You can't directly select from a table without knowing the name of the table. You can use dynamic sql to create a query if you have a table that stores all of the tables you want to select from, you can also concatenate a bunch of stuff together and create a cursor to select from. how do you know what tables you want in the select?
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width