Inner Join with Count() on Three Tables

SQL COUNT on 3 tables with JOIN

A join repeats each row in the left hand table for each row in the right hand table. If you combine multiple joins some rows will be double counted. A solution is to move the count to a subquery. For example:

select  *
from    table1 t1
join    (
        select  table1_id
        ,       count(*)
        from    table2
        group by
                table1_id
        ) t2
on      t2.table1_id = t1.id
join    (
        select  table1_id
        ,       count(*)
        from    table3
        group by
                table1_id
        ) t3
on      t3.table1_id = t1.id

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.

Inner join with count() on three tables

It makes more sense to join the item with the orders than with the people !

SELECT
    people.pe_name,
    COUNT(distinct orders.ord_id) AS num_orders,
    COUNT(items.item_id) AS num_items
FROM
    people
    INNER JOIN orders ON orders.pe_id = people.pe_id
         INNER JOIN items ON items.ord_id = orders.ord_id
GROUP BY
    people.pe_id;

Joining the items with the people provokes a lot of doublons.
For example, the cake items in order 3 will be linked with the order 2 via the join between the people, and you don't want this to happen !!

So :

1- You need a good understanding of your schema. Items are link to orders, and not to people.

2- You need to count distinct orders for one person, else you will count as many items as orders.

Join Three Tables and Use Count on One of Them

A common way to do this is make a sub-query that has counts and key then join to that. Like this:

select * 
from users
join post on post.FK_User_ID=users.User_ID
left join (
   select  FK_Post_ID, count(*) as count_of_likes_on_a_post
   from likestable 
   group by FK_Post_ID
) likes on post.Post_ID = likes.FK_Post_ID

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;

count after join on multiple tables and count of multiple column values

if you want you result as a query without CTEs this should work:

  select empName,
       empNo,   
        (select employee_details.empNo, count(employee_assignment.assId) 
        from employee_details as t1
        join employee_assignment on (t1.empno = employee_assignment.empno)
        join employee_assignment_property on (employee_assignment.assId = employee_assignment_property.assId)
        where employee_assignment.ptop = 'COMPLETED'
                and t.empNo = t1.empNo
        group by t1.empNo ) as [COMPLETED],

        (select employee_details.empNo, count(employee_assignment.assId) 
        from employee_details as t1
        join employee_assignment on (t1.empno = employee_assignment.empno)
        join employee_assignment_property on (employee_assignment.assId = employee_assignment_property.assId)
        where employee_assignment.ptop = 'STARTED'
                and t.empNo = t1.empNo
        group by t1.empNo ) as [STARTED],       
from employee_details as t

COUNT function and INNER JOIN on multiple tables

You need to include project_id in your Join to the files table and use a left join -

SELECT COUNT(file_id) as 'Pending files', projects.project_id, projects.project_name, projects.status, projects.start_date

FROM ((project_manager 
LEFT JOIN files 
ON project_manager.mag_id = files.manager_id AND project_manager.mag_id = 11 AND file_status = 'Pending' AND project_manager.project_Id = files.project_id )
INNER JOIN projects
ON projects.project_id = project_manager.project_id)

WHERE project_manager.mag_id = 11
GROUP BY projects.project_id, projects.project_name, projects.status, projects.start_date
ORDER BY projects.status, projects.start_date DESC

Using JOIN and COUNT to find totals SQL

how many clients are in each country

select country , count(*) from country  inner join client on country.id=client.country_id 
group by country 

how many clients each employee has

Select employee , count(*) from employee inner join client on client.employee_id =employee.id group by employee 

Count Column in multiple inner join

Solution :
COUNT(distinct bibliotheque.id)

---

Related Topics