Select count(*) from multiple tables

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

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

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.

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.

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
/

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

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.

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

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

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

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
)

SELECT  (
        SELECT COUNT(*)
        FROM   tbl1
        )
        +
        (
        SELECT COUNT(*)
        FROM   tbl2
        ) 
    as TotalCount