John Arellano John Arellano - 2 months ago 7
MySQL Question

Count two different rows in mysql query

I have organizations. Each organization can have members and projects.
I want to get list of organizations with number of members and projects.
For example,

Organization | Members | Projects | Action
------------------------------------------
Org 1 | 5 | 6 | Delete - Edit
Org 2 | 2 | 9 | Delete - Edit


I am using this query,

SELECT COUNT(m.id) as members, COUNT(p.id) as projects,
o.status,o.organization_name,o.logo, o.id as id
from tbl_organizations o
LEFT JOIN tbl_organization_members m ON (o.id = m.organization_id)
LEFT JOIN tbl_projects p ON (o.id = p.organization_id)
WHERE o.status= 'active' AND o.created_by= 1


But the output of number of projects is equal to number of members.
How can I make the sample above using query?

Answer

You can co-related subquery:

SELECT
    o.id as Organization,
    (SELECT COUNT(*) FROM tbl_organization_members WHERE organization_id = o.id) as members,
    (SELECT COUNT(*) FROM tbl_projects WHERE organization_id = o.id) as projects
FROM
    tbl_organizations o
WHERE
    o.status= 'active' AND o.created_by = 1
Comments