ChatGPT解决这个技术问题 Extra ChatGPT

Select count(*) from multiple tables

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

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

why do you need dual? what does that mean?
It's a fake table with one record. You can't have SELECT without FROM in Oracle.
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"
It makes no difference, Oracle won't evaluate anything inside COUNT(*).
@Stéphane: this happens when you try Oracle code on PostgreSQL. Lose the FROM dual.
P
Peter Mortensen

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


I was just getting ready to say "But what about MS SQL, when I saw your comment. Thank you for anticipating the need!
M
Mike Woodhouse

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.


You'd better put UNION ALL here.
What difference could adding "ALL" make with three single row queries? The results must be the same either way, surely?
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.
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? ;-)
This is also a good way to use a CTE (WITH SELECT) statement for each count.
N
Nic Wise

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.


V
Vikas Kumar
    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

D
David Aldridge

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
/

P
Peter Mortensen

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


I tested this also in Postgres and 'From Dual' is also unnecessary.
P
Peter Mortensen

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.
J
Jimmy Stenke

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.


c
casperOne

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


T
Taryn

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


J
Jens Schauder
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
)

S
Sheikh Kawser
--============= 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

R
Rabby Hasan
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)

z
zloctb

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

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

This is not what OP asked. This calculates the overall sum.
C
Cris

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


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

Welcome to StackOverflow and thanks for posting. Please have a look at How to Answer.
This answer is wrong. Cannot use union (should use union all).