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

COLD TOLD · Accepted Answer · 2012-04-17 05:29:42Z

11

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

answered Apr 17, 2012 at 5:29

COLD TOLD

13.6k3 gold badges37 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tikej Over a year ago

that simply elegant solution beats it all IMO

Florin Ghita · Accepted Answer · 2012-04-17 06:41:45Z

4

You can't do it directly with a query like where table in (myTableA, myTableB, etc) But you can pretify the union all solution:

select MyTableName, count(*)
FROM(
  select 'myTableA' MyTableName, timestamp from mytableA 
  union all
  select 'myTableB', timestamp  from mytableB  
  union all
  select 'myTableA', timestamp  from mytableC  
)
WHERE timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
GROUP BY MyTableName;

edited Apr 17, 2012 at 6:41

answered Apr 17, 2012 at 5:47

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

1 Comment

cjd143SD Over a year ago

thanks. this is much prettier. I did have to add GROUP BY MyTableName to get it to work. Thanks.

arun.v1 · Accepted Answer · 2012-04-17 05:26:25Z

3

I am not sure about Oracle, in SQLserver you can do this as,

select (select count(*) from table1) + (select count(*) from table2)

Update: or like this,

select (select count(*) from table1) ,(select count(*) from table2)

OR,

(select count(*) from table1) union (select count(*) from table2)

answered Apr 17, 2012 at 5:26

arun.v1

4721 gold badge5 silver badges12 bronze badges

Comments

Mikael Eriksson · Accepted Answer · 2012-04-17 05:28:39Z

1

select 'myTableA' MyTableName, count(*) MyCnt from mytableA where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
union all
select 'myTableB', count(*) from mytableB where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
union all
select 'myTableC', count(*) from mytableC where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000';

answered Apr 17, 2012 at 5:28

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Comments

leanne · Accepted Answer · 2013-06-14 20:08:46Z

1

Using Oracle 11gR2:

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

Results in:

tabA| tabB| tabC
----|-----|-----
 121|   78| 2345

edited Jun 14, 2013 at 20:08

answered Jun 14, 2013 at 19:59

leanne

8,9091 gold badge57 silver badges82 bronze badges

Comments

hkutluay · Accepted Answer · 2012-04-17 05:28:42Z

0

Try this

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

answered Apr 17, 2012 at 5:28

hkutluay

6,9742 gold badges37 silver badges57 bronze badges

Comments

aquatorrent · Accepted Answer · 2012-04-17 05:46:57Z

-3

How about this?

SELECT 
     a, b, c 
FROM 
     (select count(*) from mytableA where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000') AS a, 
     (select count(*) from mytableB where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000') AS b, 
     (select count(*) from mytableC where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000') AS c

dunno whether this works or not though :/

edited Apr 17, 2012 at 5:46

answered Apr 17, 2012 at 5:28

aquatorrent

8412 gold badges8 silver badges11 bronze badges

1 Comment

Vishal Nagda Over a year ago

Please do not answer blindly

Collectives™ on Stack Overflow

sql count records in from multiple tables using single query

7 Answers 7

1 Comment

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related