How to get the count of two fields from two different tables with grouping a field from another table in mysql
I have three tables projects
, discussions
and comments
.
I tried it this way:
SELECT p.PRO_Name, COUNT( d.DIS_Id ) AS nofdisc, COUNT( c.COM_Id ) AS nofcom
FROM projects p
LEFT JOIN discussions d ON p.PRO_Id = d.PRO_Id
LEFT JOIN comments c ON d.DIS_Id = c.DIS_Id
GROUP BY p.PRO_Name LIMIT 0 , 30
But it takes all the lines from discussions
and the counter is the comments
same as the number discussions
.
source to share
count
counts the number of values not for null
this parameter. Joining that you created, create a row for comments, where dis_id
and com_id
are not null
, therefore, their calculations are the same. Since they are identifiers, you can simply count the number of occurrences to get the answer you want: distinct
(EDIT: Added suggestion order by
as requested in the comments)
SELECT p.PRO_Name,
COUNT(DISTINCT d.DIS_Id) AS nofdisc,
COUNT(DISTINCT c.COM_Id) AS nofcom
FROM projects p
LEFT JOIN discussions d ON p.PRO_Id = d.PRO_Id
LEFT JOIN comments c ON d.DIS_Id = c.DIS_Id
GROUP BY p.PRO_Name
ORDER BY 2,3
LIMIT 0 , 30
source to share