sql count records in from multiple tables using single query

Question

I have a list of tables for example:

mytableA
mytableB
mytableC

The tables all have same column (timestamp).

I can do a count on each table individually:

select count(*) from mytableA where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000';
select count(*) from mytableB where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000';
select count(*) from mytableC where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000';

How can I combine this in one single query? Is there an easy way?

Expected Results:

MyTableName     MyCnt
-----------     -----
mytableA        121
mytableB        78
mytableC        2345

Answer 1

SELECT  (
    SELECT COUNT(*)
    FROM   table1
    ) AS tot1,
    (
    SELECT COUNT(*)
    FROM   table2
    ) AS tottab2,
    (
    SELECT COUNT(*)
    FROM   table3
    ) AS tottab3