Select count(*) from multiple tables

Question

Ho to all! How can I select count(*) fro two different tables (call them tab1 and tab2) having as resulta:

Count_1   Count_2
123       456

? I've tried this:

select count(*) Count_1 from schema.tab1 union all select count(*) Count_2 from schema.tab2

but all i have is:

Count_1
123
456

Thanx in advance

Answer 1

SELECT  (
        SELECT COUNT(*)
        FROM   tab1
        ) AS count1,
        (
        SELECT COUNT(*)
        FROM   tab2
        ) AS count2
FROM    dual

Answer 2

select (select count(*) from tab1) count_1, (select count(*) from tab2) count_2 from dual;

Answer 3

A quick stab came up with:

Select (select count() from Table1) as Count1, (select count() from Table2) as Count2

[Note: I tested this in SQL Server, so From Dual is not necessary (hence the discrepancy)]

Answer 4

As an additional info, to accomplish same thing in SQL Server, you just need to remove "FROM dual" part of the query.

Answer 5

My experience is with SQL Server, but could you do:

select (select count(*) from table1) as count1,
  (select count(*) from table2) as count2

In SQL Server I get the result you are after.

Answer 6

If the tables (or at least a key column) are of the same type just make the union first and then count.

select count(*) 
  from (select tab1key as key from schema.tab1 
        union all 
        select tab2key as key from schema.tab2
       )

Or take your satement and put another sum() around it.

select sum(amount) from
(
select count(*) amount from schema.tab1 union all select count(*) amount from schema.tab2
)

Answer 7

Just because it's slightly different:

SELECT 'table_1' AS table_name, COUNT(*) FROM table_1
UNION
SELECT 'table_2' AS table_name, COUNT(*) FROM table_2
UNION
SELECT 'table_3' AS table_name, COUNT(*) FROM table_3

It gives the answers transposed (one row per table instead of one column), otherwise I don't think it's much different. I think performance-wise they should be equivalent.

Answer 8

Other slightly different methods:

with t1_count as (select count(*) c1 from t1),
     t2_count as (select count(*) c2 from t2)
select c1,
       c2
from   t1_count,
       t2_count
/

select c1,
       c2
from   (select count(*) c1 from t1) t1_count,
       (select count(*) c2 from t2) t2_count
/

Answer 9

As I can't see any other answer bring this up.

If you don't like sub-queries and have primary keys in each table you can do this:

select count(distinct tab1.id) as count_t1,
       count(distinct tab2.id) as count_t2
    from tab1, tab2

But performance wise I believe that Quassnoi's solution is better, and the one I would use.

Answer 10

Thanks a lot.. Was looking for a solution to this

Answer 11

SELECT (SELECT COUNT() FROM table1) + (SELECT COUNT() FROM table2) FROM dual;

Answer 12

Here is from me to share

Option 1 - counting from same domain from different table

select distinct(select count() from domain1.table1) "count1", (select count() from domain1.table2) "count2" from domain1.table1, domain1.table2;

Option 2 - counting from different domain for same table

select distinct(select count() from domain1.table1) "count1", (select count() from domain2.table1) "count2" from domain1.table1, domain2.table1;

Option 3 - counting from different domain for same table with "union all" to have rows of count

select 'domain 1'"domain", count() from domain1.table1 union all select 'domain 2', count() from domain2.table1;

Enjoy the sql, I always do :)

Answer 13

For a bit of completeness - this query will create a query to give you a count of all of the tables for a given owner.

select 
  DECODE(rownum, 1, '', ' UNION ALL ') || 
  'SELECT ''' || table_name || ''' AS TABLE_NAME, COUNT(*) ' ||
  ' FROM ' || table_name  as query_string 
 from all_tables 
where owner = :owner;

The output is something like

SELECT 'TAB1' AS TABLE_NAME, COUNT(*) FROM TAB1
 UNION ALL SELECT 'TAB2' AS TABLE_NAME, COUNT(*) FROM TAB2
 UNION ALL SELECT 'TAB3' AS TABLE_NAME, COUNT(*) FROM TAB3
 UNION ALL SELECT 'TAB4' AS TABLE_NAME, COUNT(*) FROM TAB4

Which you can then run to get your counts. Its just a handy script to have around sometimes