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

Quassnoi · Accepted Answer · 2009-03-05T09:52:36.733

423

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

edited Mar 05 '09 at 09:52

answered Mar 03 '09 at 12:39

Quassnoi

413,100
91
616
614

21

why do you need dual? what does that mean? – Ray Lu Mar 03 '09 at 12:44
36

It's a fake table with one record. You can't have SELECT without FROM in Oracle. – Quassnoi Mar 03 '09 at 12:45
3

dual is a table in oracle db's to which all accounts can access you can use it for common needs like: "SELECT sysdate FROM dual" – dincerm Mar 03 '09 at 12:47
@Quassnoi, oh I see for Oracle. 1up – Ray Lu Mar 03 '09 at 12:47
Dont you think using SELECT COUNT(1) is a bit more efficient than SELECT COUNT(*)? – Ray Lu Mar 03 '09 at 12:49
5

It makes no difference, Oracle won't evaluate anything inside COUNT(*). – Quassnoi Mar 03 '09 at 12:57
The DUAL table is my number one annoyance with Oracle SQL: http://stackoverflow.com/questions/282329/what-are-five-things-you-hate-about-your-favorite-language/564388#564388 – Jon 'links in bio' Ericson Mar 03 '09 at 20:31
Use any table you want then - (ALL_TABLES WHERE ROWNUM = 1) whatever. But Dual in 10g has no i/o – Mar 04 '09 at 16:42
Note that DB/2 does not support this syntax. – Thorbjørn Ravn Andersen Jan 31 '14 at 14:20
6

@Stéphane: this happens when you try Oracle code on PostgreSQL. Lose the `FROM dual`. – Quassnoi Feb 03 '16 at 12:52
how can I make a group by from the result of those 2 select? – sliders_alpha Sep 05 '16 at 09:41
@sliders_alpha: please post it as another question, providing some sample data and desired resultset. – Quassnoi Sep 05 '16 at 12:30
For MySql, remove the "FROM dual". – Blisterpeanuts Dec 14 '21 at 18:41

score 100 · Answer 2 · edited Aug 18 '11 at 21:44

100

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

edited Aug 18 '11 at 21:44

Peter Mortensen

30,738
21
105
131

answered Mar 03 '09 at 12:45

dincerm

1,455
1
9
9

3

I was just getting ready to say "But what about MS SQL, when I saw your comment. Thank you for anticipating the need! – Andrew Neely May 29 '12 at 12:52

score 64 · Answer 3 · answered Mar 03 '09 at 16:51

64

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.

answered Mar 03 '09 at 16:51

Mike Woodhouse

51,832
12
88
127

2

You'd better put UNION ALL here. – Quassnoi Mar 03 '09 at 20:31
What difference could adding "ALL" make with three single row queries? The results must be the same either way, surely? – Mike Woodhouse Mar 04 '09 at 07:04
1

UNION without ALL groups results. If there are 2 rows in table_1 and table_2, and 3 rows in table_3, you'll get two rows in your resultset, and won't be able to tell from resultset how many rows does table_2 have: 2 or 3. – Quassnoi Mar 04 '09 at 10:40
6

Yes, but I select the table name, which makes the results unique. Otherwise you'd be correct, but what value would there be in several numbers without context? ;-) – Mike Woodhouse Mar 04 '09 at 12:17
This is also a good way to use a CTE (WITH SELECT) statement for each count. – blue_chip Sep 16 '19 at 21:06

score 36 · Answer 4 · answered Mar 03 '09 at 12:45

36

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.

answered Mar 03 '09 at 12:45

Nic Wise

8,061
2
31
30

score 17 · Answer 5 · answered Jun 02 '17 at 22:07

17

    select 
    t1.Count_1,t2.Count_2
    from 
(SELECT count(1) as Count_1 FROM tab1) as t1, 
(SELECT count(1) as Count_2 FROM tab2) as t2

answered Jun 02 '17 at 22:07

Vikas Kumar

1,683
14
15

score 17 · Answer 6 · answered Mar 03 '09 at 20:25

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
/

score 11 · Answer 7 · edited Aug 18 '11 at 21:43

11

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

edited Aug 18 '11 at 21:43

Peter Mortensen

30,738
21
105
131

answered Mar 03 '09 at 12:41

CJM

11,908
20
77
115

I tested this also in Postgres and 'From Dual' is also unnecessary. – sianipard May 24 '21 at 08:49

score 9 · Answer 8 · edited Aug 18 '11 at 21:46

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.

I like the answer but I think you should add the owner to the "FROM"-part of the query. Otherwise you have to execute the query with the owner which might not directly be available. — Christopher Gertz, Mar 17 '21 at 16:05

score 8 · Answer 9 · edited Jan 11 '12 at 13:21

8

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

edited Jan 11 '12 at 13:21

casperOne

73,706
19
184
253

answered Jul 09 '09 at 07:01

score 8 · Answer 10 · edited Feb 24 '12 at 17:27

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

score 8 · Answer 11 · answered Mar 05 '09 at 10:09

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.

score 6 · Answer 12 · answered Apr 28 '17 at 12:11

--============= FIRST WAY (Shows as Multiple Row) ===============
SELECT 'tblProducts' [TableName], COUNT(P.Id) [RowCount] FROM tblProducts P
UNION ALL
SELECT 'tblProductSales' [TableName], COUNT(S.Id) [RowCount] FROM tblProductSales S


--============== SECOND WAY (Shows in a Single Row) =============
SELECT  
(SELECT COUNT(Id) FROM   tblProducts) AS ProductCount,
(SELECT COUNT(Id) FROM   tblProductSales) AS SalesCount

score 6 · Answer 13 · answered Mar 03 '09 at 12:39

6

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

answered Mar 03 '09 at 12:39

Jens Schauder

77,657
34
181
348

score 5 · Answer 14 · answered Mar 03 '09 at 12:47

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
)

score 2 · Answer 15 · answered Aug 03 '16 at 19:14

2

Declare @all int
SET @all = (select COUNT(*) from tab1) + (select count(*) from tab2)
Print @all

or

SELECT (select COUNT(*) from tab1) + (select count(*) from tab2)

answered Aug 03 '16 at 19:14

Rabby Hasan

356
4
10

score 0 · Answer 16 · answered Aug 16 '15 at 05:35

0

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

answered Aug 16 '15 at 05:35

zloctb

10,592
8
70
89

Chag · Answer 17 · 2021-12-28T10:45:11.377

0

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

edited Dec 28 '21 at 10:45

answered Dec 28 '21 at 10:39

Chag

31
5

This is not what OP asked. This calculates the overall sum. – derteufelqwe Jul 08 '22 at 11:43

score 0 · Answer 18 · answered Feb 22 '23 at 09:30

If you're using Google BigQuery this will work.

SELECT
  date,
  SUM(Table_1_Id_Count) AS Table_1_Id_Count,
  SUM(Table_2_Id_Count) AS Table_2_Id_Count
FROM
  (
    SELECT
      Id AS Table_1_Id,
      date,
      COUNT(Id) AS Table_1_Id_Count,
      0 AS Table_2_Id_Count
    FROM
      `your_project_name.Table_1`
    GROUP BY
      Id,
      date
    UNION ALL
    SELECT
      Id AS Table_2_Id,
      date,
      0 AS Table_1_Id_Count,
      COUNT(Id) AS Table_2_Id_Count
    FROM
      `your_project_name.Table_2`
    GROUP BY
      Id,
      date
  )
GROUP BY
  date

score 0 · Answer 19 · answered Jul 27 '23 at 20:24

You need subqueries:

Select t1.key, t1.col, t2.col, 
    , (Select count(*)
        From table3
        Where key = t1.key) as Count1
    , (Select count(*)
        From table4
        Where key = t1.key) as Count2
From table1 t1
Join table2 t2 on t2.key = t1.key
Where t1.key in (1,2,3)

score -1 · Answer 20 · answered May 24 '17 at 20:11

-1

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

answered May 24 '17 at 20:11

Cris

2,824
24
23

score -2 · Answer 21 · edited Jun 25 '11 at 01:10

-2

select @count = sum(data) from
(
select count(*)  as data from #tempregion
union 
select count(*)  as data from #tempmetro
union
select count(*)  as data from #tempcity
union
select count(*)  as data from #tempzips
) a

edited Jun 25 '11 at 01:10

Taryn

242,637
56
362
405

answered Jun 24 '11 at 10:46

sajid

1

Welcome to StackOverflow and thanks for posting. Please have a look at [How to Answer](http://stackoverflow.com/questions/how-to-answer). – Serge Belov Nov 18 '12 at 21:09
This answer is wrong. Cannot use union (should use union all). – Deadsheep39 Jun 25 '19 at 09:07

Select count(*) from multiple tables

21 Answers21

Linked

Related