SQL Query to Count() 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;

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"

COUNT Multiple tables with relationships and users

your over simplification seems to have lost your what appears to be your issue from some of your comments. Using left joins would include any of the users even if they don't have a count of one of your other tables. However if you want your result set to only include usres that have at least 1 Incident and/or 1 Request and/or 1 Change Request. Etc. you can either filter the aggretation you are doing after the fact to remove when Incidents + Requests + ... = 0. Or you can filter them out by adding a WHERE statement that says WHEN NOT all of those other tables are null which is the same as OR IS NOT NULL...

 SELECT 
 u.DisplayName as Analyst, 
 u.BaseManagedEntityId as AUsername,
 COUNT(distinct i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) AS 'Active Incidents',
 COUNT(distinct sr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) as 'Active Service Requests',
 COUNT(distinct cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) as 'Active Change Requests',
 COUNT(distinct ma.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) as 'Active Manual Activities'
 FROM
    MTV_System$Domain$User u 
    INNER JOIN RelationshipView r
    ON r.TargetEntityId = u.BaseManagedEntityId
    AND r.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' 
    AND  r.IsDeleted ='0'
    LEFT JOIN  MTV_System$WorkItem$Incident i
    ON r.SourceEntityId = i.BaseManagedEntityId
    AND i.Status_785407A9_729D_3A74_A383_575DB0CD50ED NOT IN ('2B8830B6-59F0-F574-9C2A-F4B4682F1681','BD0AE7C4-3315-2EB3-7933-82DFC482DBAF')
    LEFT JOIN MTV_System$WorkItem$ServiceRequest sr
    ON r.SourceEntityId = SR.BaseManagedEntityId
    AND sr.Status_6DBB4A46_48F2_4D89_CBF6_215182E99E0F IN ('72B55E17-1C7D-B34C-53AE-F61F8732E425','59393F48-D85F-FA6D-2EBE-DCFF395D7ED1','05306BF5-A6B9-B5AD-326B-BA4E9724BF37')
    LEFT JOIN MTV_System$WorkItem$ChangeRequest cr
    ON r.SourceEntityId = cr.BaseManagedEntityId
    AND cr.Status_72C1BC70_443C_C96F_A624_A94F1C857138 IN ('6D6C64DD-07AC-AAF5-F812-6A7CCEB5154D','DD6B0870-BCEA-1520-993D-9F1337E39D4D')
    LEFT JOIN MTV_System$WorkItem$Activity$ManualActivity MA
    ON r.SourceEntityId = ma.BaseManagedEntityId
    AND ma.Status_8895EC8D_2CBF_0D9D_E8EC_524DEFA00014 IN ('11FC3CEF-15E5-BCA4-DEE0-9C1155EC8D83','D544258F-24DA-1CF3-C230-B057AAA66BED')
WHERE
    i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C IS NOT NULL
    OR sr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C IS NOT NULL
    OR cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C IS NOT NULL
    OR ma.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C IS NOT NULL
GROUP BY u.DisplayName,u.BaseManagedEntityId
 Order by u.DisplayName

Also note the user of IN and NOT IN in the join conditions instead of OR all of the time.

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;

Inner join with Count, multiple tables

As I don't succeed to get a result with Gordon Linoff answer, I tried an other way with LEFT OUTER JOIN.

Thanks to this post :
How to get a group where the count is zero

I managed to get a correct result with void relations (for example, an item Table2 has no reference in Table3), but now I got an incorrect result when I join more than 2 tables.

I guess it's a problem from my request...

Currently :

SELECT Table1.UID, Table1.Name
COUNT(Table2.UID) AS CountTable2
FROM Table1
LEFT OUTER JOIN Table2 ON Table2.FK_Table1 = Table1.UID
GROUP BY Table1.UID, Table1.Name

gives me a correct result (only two tables related), but :

SELECT Table1.UID, Table1.Name
COUNT(Table2.UID) AS CountTable2, COUNT(Table3.UID) AS CountTable3
FROM (Table1
LEFT OUTER JOIN Table2 ON Table2.FK_Table1 = Table1.UID)
LEFT OUTER JOIN Table3 ON Table3.FK_Table2 = Table2.UID
GROUP BY Table1.UID, Table1.Name

gives me an incorrect result for CountTable2, which appears to be more than expected. CountTable3 is correct.

EDIT :

I finally figure how to make it works, according to my research and the hint from Gordon Linoff with aggregation before joining.

I start by counting the deepest table inside the table above, then join, and so on. At every step, I select the essentials informations to keep : UID, FK_AboveTable, Count, Sums from deepest table.

Final code :

SELECT Table1.UID, Table1.Name, COUNT(Table2.UID) AS TotalTable2, SUM(CountTable3) AS TotalTable3, SUM(CountTable4_2) AS TotalTable4
FROM Table1 LEFT OUTER JOIN (
    SELECT Table2.UID, Table2.FK_Table1, COUNT(Table3.UID) AS CountTable3, 
    SUM(CountTable4) AS CountTable4_2
    FROM Table2 LEFT OUTER JOIN (
        SELECT Table3.UID, Table3.FK_Table1, COUNT(Table4.UID) AS CountTable4
        FROM Port LEFT OUTER JOIN
            Table4 ON Table4.FK_Table3 = Table3.UID
            GROUP BY Table3.UID, Table3.FK_Table2
        ) Table3 ON Table3.FK_Table2 = Table2.UID
        GROUP BY Table2.UID, Table2.FK_Table1
    ) Table2 ON Table2.FK_Table1= Table1.UID
GROUP BY Table1.UID, Table1.Name ORDER BY Table1.Name DESC

Note that void count from deepest table appears are void and no 0 (for example, if there is one item in Table1, none related in Table2, the count will be 0 for Table2, void for Table3 and Table4).

I assume this might be upgraded, but for now it solves my issue, and allows me to add as many tables as I need.

How to count records in multiple tables containing the same column?

I doubt "EXEC sp_MSForEachTable" solution will not work, assuming the database has few more table, which don't have that User Id column, unless you are explicitly handling such failure using try catch block. In that case It will surely fail.

Here the solution to consider only those table which have the required column.

    --To get the List of Table having the required column and Storing them into Temp Table.
Select ID = IDENTITY(Int,1,1),Object_Name(object_id) As TableName Into #ReqTables
From sys.columns where name = 'Crets'

    --Creating Table to Store Row count result.
Create Table #RowCounts
(
    Row_Count Int
    , UserID Int
)

    --Declaring variables
Declare @min Int,@max int,@TableName Nvarchar(255)
    --get min and Max values from Required table for looping
Select @min = Min(Id),@max = Max(ID) From #ReqTables

    --loop through Min and Max
While(@min <= @max)
BEgin
        --get the table for a given loop Counter
    Select @TableName = tableName From #ReqTables Where Id = @min
        --Executing the Dynamic SQl
    Exec ('Insert Into #RowCounts (Row_Count,UserID) Select Count(*),UserID From ' + @TableName + ' Group by UserID')
        --incrementing the Counter
    Set @min = @min + 1
End

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