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
Instead of Null How to Show '0' in Result with Select Statement SQL
Do Ddl Statements Always Give You an Implicit Commit, or Can You Get an Implicit Rollback
How to View All the Metadata of Columns of a Table in Oracle Database
Finding Rows with Consecutive Increase in the Values of a Column
Add Column to Table and Then Update It Inside Transaction
SQL Get the Last Date Time Record
Convert Timestamp to Date in Oracle SQL
Select Only Some Columns from a Table on a Join
Count of Non-Null Columns in Each Row
Case Statement in SQL, How to Return Multiple Variables
In MySQL: How to Pass a Table Name as Stored Procedure And/Or Function Argument
SQL Server Management Studio - How to Change a Field Type Without Dropping Table
Deleting Duplicates Rows from Redshift
Impact of Defining Varchar2 Column with Greater Length