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


Solution:1

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


Solution:2

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


Solution:3

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.


Solution:4

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.


Solution:5

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  /  


Solution:6

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.


Solution:7

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


Solution:8

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


Solution:9

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


Solution:10

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


Solution:11

    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  


Solution:12

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  )  


Solution:13

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.


Solution:14

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)  


Solution:15

--============= 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  


Solution:16

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


Solution:17

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


Solution:18

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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »