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

User · Answer

Count Rows from two Tables SELECT  SELECT COUNT    FROM table1              SELECT COUNT    FROM table2    AS total FROM dual

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

select  select count    from tab1  count 1   select count    from tab2  count 2 from dual

User · Answer

SELECT            SELECT COUNT            FROM   tab1           AS count1                    SELECT COUNT            FROM   tab2           AS count2 FROM    dual

User · Answer

select  select count   from tab1 where field like  value      select count   from tab2 where field like  value   count

User · Answer

SELECT   SELECT COUNT    FROM table1     SELECT COUNT    FROM table2  FROM    dual

[sql] Select count(*) from multiple tables

Examples related to sql

Examples related to oracle

Examples related to count