Sql: Combine Select Count(*) from Multiple Tables

SQL: Combine Select count(*) from multiple tables

SELECT 
(select count(*) from foo1 where ID = '00123244552000258')
+
(select count(*) from foo2 where ID = '00123244552000258')
+
(select count(*) from foo3 where ID = '00123244552000258')

This is an easy way.

Select multiple count(*) in multiple tables with single query

A more traditional approach is to use "derived tables" (subqueries) so that the counts are performed before joins multiply the rows. Using left joins allows for all id's in basic to be returned by the query even if there are no related rows in either joined tables.

select
      basic.id
    , coalesce(a.LinkACount,0) LinkACount
    , coalesce(b.linkBCount,0) linkBCount
from basic 
left join (
     select id, Count(linkA_ID) LinkACount from LinkA group by id
     ) as a on a.id=basic.id
left join (
     select id, Count(linkB_ID) LinkBCount from LinkB group by id
     ) as b on b.id=basic.id

MySQL join and COUNT() on multiple tables

You will need to use DISTINCT, but also you need to count the IDs, not the foreign keys:

SELECT
    table1.name,
    COUNT(DISTINCT table2.id) AS table2_count,
    COUNT(DISTINCT table3.id) AS table3_count,
    COUNT(DISTINCT table4.id) AS table4_count,
    SUM(table4.size) AS table4_size
FROM table1
LEFT JOIN table2 ON table1.id = table2.table1_id
LEFT JOIN table3 ON table2.id = table3.table2_id
LEFT JOIN table4 ON table3.id = table4.table3_id
WHERE table1.id = 1

Here is a fiddle.

Explanation: The DISTINCT key word eliminates all duplicate values resulting in a list of unique values.

If you run your query without the COUNT() and SUM(), you get:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1024 
test  1          1          1          200 
test  1          (null)     (null)     (null) 
test  1          (null)     (null)     (null) 

So if you add the COUNT() and SUM(), you obviously get:

name  table1_id  table2_id  table3_id  size
test  4          2          2          1224 

However, using DISTINCT with your query won't help because you can clearly see the duplicate values, which will result in:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1224 

Now, if you run my query without the COUNT() and SUM(), you get:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1024 
test  1          1          2          200 
test  2          (null)     (null)     (null) 
test  3          (null)     (null)     (null) 

If you add the COUNT() and SUM(), you get exactly the same results like your query:

name  table1_id  table2_id  table3_id  size
test  4          2          2          1224 

However, because this time you have different values (i.e. not all are 1), so now if you count the unique values using DISTINCT, you get:

name  table1_id  table2_id  table3_id  size
test  3          1          2          1224 

SQL: How to combine count results from multiple tables into multiple columns

Depending on the database RDBM you are, a few things could change in syntax. In the ANSI Sql definition your query should be:

select col1, col2 FROM
    (SELECT COUNT(DISTINCT col1) as col1 from table1) as tab1,
    (SELECT COUNT(DISTINCT col2) as col22 from table2) as tab2

You have to add alias for all sub queries. Also name your columns with words, not number, it is easier to understand. Though I don't recall if a number is not allowed as an alias in SQL ANSI.

Without aliases for the subqueries you can use like this:

-- For MySql, PostgreSql, SQL Server (not sure though)
select (SELECT COUNT(DISTINCT col1)
          from table1) as col1,
       (SELECT COUNT(DISTINCT col2) as col22 
          from table2) as col2

-- For Oracle
select (SELECT COUNT(DISTINCT col1)
          from table1) as col1,
       (SELECT COUNT(DISTINCT col2) as col22 
          from table2) as col2
  from dual

-- For DB2
select (SELECT COUNT(DISTINCT col1)
          from table1) as col1,
       (SELECT COUNT(DISTINCT col2) as col22 
          from table2) as col2
  from sysibm.sysdummy1

Side note: you can use a number as an alias if you surround it with double quotes " (this is SQL ANSI and will work everywhere) like this:

select "1", "2" FROM
    (SELECT COUNT(DISTINCT col1) as "1" from table1) a, --don't forget the table alias
    (SELECT COUNT(DISTINCT col2) as "2" from table2) b

Mysql Also allows you to use back ticks:

select `1`, `2` FROM
    (SELECT COUNT(DISTINCT col1) as `1` from table1) a,
    (SELECT COUNT(DISTINCT col2) as `2` from table2) b

SQL Sum and Count JOIN Multiple tables

Seems the only thing i need is to subquery it

select a.*, d.image, b.totalRating, c.totalUser from places a, ( select place_id, sum(rating) AS totalRating from ratings group by place_id ) b, ( select place_id, count(id) AS totalUser from ratings group by place_id ) c, places_images d where c.place_id = a.id and b.place_id = a.id and d.place_id = a.id GROUP BY a.id

Result

Thank you very much, GBU

SQL select count from multiple tables

You want to use union all to combine the tables and then aggregate them. I might recommend:

select order_id, sum(ordered) as ordered, sum(exchanged) as exchanged,
       sum(exchanged + ordered) as total
from ((select order_id, amount as ordered, 0 as exchanged
       from order_products
      ) union all
      (select order_id, 0 as ordered, amount as exchanged
       from exhange_products
      )
     ) oe
group by order_id;

It is important to use union all rather than union, because union removes duplicates (which can result in bad numbers). Union also incurs overhead that is unnecessary.

And, by "count amount" I assume you really mean to take the sum.

Select count(*) from multiple tables with null values

And another approach here

declare @table1 table (class varchar(1) null, val int)
declare @table2 table (class varchar(1) null, val int)

insert into @table1 values ('A', 1)
insert into @table1 values ('A', 1)
insert into @table1 values ('A', 1)
insert into @table1 values ('B', 1)
insert into @table1 values ('A', 1)
insert into @table1 values ('B', 1)
insert into @table1 values ('C', 1)

insert into @table2 values ('A', 1)
insert into @table2 values ('A', 1)
insert into @table2 values ('A', 1)
insert into @table2 values ('B', 1)

select t.Class, 
       (select count(val) from @table1 where class = t.class),
       (select count(val) from @table2 where class = t.class)
from  ( select class 
        from   @table1 t1
        union 
        select class
        from   @table2 t1
      ) t 

this returns

Class   T1  T2  
A       4   3   
B       2   1   
C       1   0   

Select count(*) from multiple tables

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

---

Related Topics