Select count(*) from multiple tables

Question

How can I select count(*) from two different tables (call them tab1 and tab2) having as result:

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

Answer 1

JOIN with different tables

SELECT COUNT(*) FROM (  
SELECT DISTINCT table_a.ID  FROM table_a JOIN table_c ON table_a.ID  = table_c.ID   );

Answer 2

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. It's just a handy script to have around sometimes.

Answer 3

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

Answer 4

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

select (select count() from tab1 where field like 'value') + (select count() from tab2 where field like 'value') count