Select Count(*) from Multiple Tables

Select count(*) from multiple tables

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

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

SQL : Show the count of multiple tables in one screen

One simple way would be to union your queries:

SELECT 'Table_1' Table_Name, count(*) "Count" FROM Table_1
union all
SELECT 'Table_2', count(*) FROM Table_2
union all
SELECT 'Table_3', count(*) FROM Table_3;

Mysql Query select count(*) from multiple tables

Within the various selects, keep the same column order.

So

SELECT   a1.id as movie_id,
         a1.movie_title as movie_title,
         COUNT(*) AS free_cnt
         0 as plus_cnt,
         0 as visitor_cnt
FROM     movie_request a1
GROUP BY a1.movie_title

UNION ALL

SELECT   a2.id as movie_id,
         a2.movie_title as movie_title,
         0 as free_cnt,
         COUNT(*) AS plus_cnt,
         0 as visitor_cnt
FROM     movie_request_2 a2
GROUP BY a2.movie_title

UNION ALL

SELECT   a3.id as movie_id,
         a3.movie_title as movie_title,
         0 as plus_cnt,
         0 as free_cnt,
         COUNT(*) AS visitor_cnt
FROM     movie_request_3 a3
GROUP BY a3.movie_title

SQL - Find Record count for multiple tables at a time in snowflake

You can use union all:

select 'Fact_MASTER', COUNT(*) from  "Fact_MASTER " union all
select 'Dim_MASTER', COUNT(*) from  "Dim_MASTER " union all
select 'Fact2', COUNT(*) from  "Fact2 " union all
select 'Dim2', COUNT(*) from  "Dim2" union all
select 'Fact3', COUNT(*) from  "Fact3" union all
select 'Dim3', COUNT(*) from  "Dim3"

Laravel: Multiple count for different tables

Write your code like in single query.

$data = DB::select("SELECT (SELECT COUNT(*) FROM posts WHERE published = true) as post_count, (SELECT COUNT(*) FROM pages WHERE published = true) as page_count, (SELECT COUNT(*) FROM products WHERE published = true) as product_count");

After that you can get record like

$data->post_count
$data->page_count
$data->product_count

Group By Count from Multiple Tables with conditions

You need to nest your query into a subquery so that you can take the average value of the counts and compare the current count with it. If you're using an SQL that supports CTEs, you can use one e.g.

WITH cnts AS (
  SELECT country.country_name, city.city_name, COUNT(customer.city_id) AS cnt 
  FROM country 
  JOIN city on country.id = city.country_id 
  JOIN customer on city.id = customer.city_id
  GROUP BY city_name,country.country_name
)
SELECT *
FROM cnts
WHERE cnt > (SELECT AVG(cnt) FROM cnts)

Otherwise the query becomes more complex with the main query required as a subquery in the WHERE clause as well:

SELECT country.country_name, city.city_name, COUNT(customer.city_id) AS cnt 
FROM country 
JOIN city on country.id = city.country_id 
JOIN customer on city.id = customer.city_id
GROUP BY city_name,country.country_name
HAVING COUNT(customer.city_id) > (SELECT AVG(cnt) FROM (
  SELECT country.country_name, city.city_name, COUNT(customer.city_id) AS cnt 
  FROM country 
  JOIN city on country.id = city.country_id 
  JOIN customer on city.id = customer.city_id
  GROUP BY city_name,country.country_name
) cnts2)

In both cases the output for your sample data is:

country_name    city_name   cnt
Brazil          Rio         3
US              Dallas      2

Demo on dbfiddle

Selecting Counts from Different Tables with a Subquery

This is a case where I would recommend correlated subqueries:

select a.userid, 
       (select count(*) from table1 t1 where t1.userid = a.userid) as cnt1,
       (select count(*) from table2 t2 where t2.userid = a.userid) as cnt2
from allusers a
where a.vip is not null;

The reason that I recommend this approach is because you are filtering the alllusers table. That means that the pre-aggregation approach may be doing additional, unnecessary work.

How to count records after joining multiple tables in SQL

An alternative approach is to use APPLY in the FROM to get the counts:

USE mydb;


SELECT emp.ecode,
       Sc.schedulecount,
       O.noordercount,
       St.salescount
FROM dbo.employee emp
     CROSS APPLY (SELECT COUNT(*) AS schedulecount
                  FROM dbo.schedule sch
                  WHERE sch.user_id = emp.ecode) Sc
     CROSS APPLY (SELECT COUNT(*) AS noordercount
                  FROM dbo.[order] ord --Generally it's a good idea to avoid Reserved Keywords for Object names
                  WHERE ord.ecode = emp.ecode) O
     CROSS APPLY (SELECT COUNT(*) AS salescount
                  FROM dbo.store sto
                  WHERE sto.ecode = emp.ecode) St
ORDER BY emp.ecode DESC;

db<>fiddle showing results are correct per question.

Crystal ball:

SELECT emp.ecode,
       Sc.schedulecount,
       O.noordercount,
       St.salescount
FROM dbo.employee emp
     CROSS APPLY (SELECT COUNT(*) AS schedulecount
                  FROM dbo.schedule sch
                  WHERE sch.user_id = emp.ecode) Sc
     CROSS APPLY (SELECT COUNT(*) AS noordercount
                  FROM dbo.[order] ord --Generally it's a good idea to avoid Reserved Keywords for Object names
                  WHERE ord.ecode = emp.ecode) O
     CROSS APPLY (SELECT COUNT(*) AS salescount
                  FROM dbo.store sto
                  WHERE sto.ecode = emp.ecode) St
WHERE Sc.schedulecount > 0  
   OR O.noordercount > 0
   OR St.salescount > 0
ORDER BY emp.ecode DESC;

---

Related Topics