John Arellano John Arellano - 1 year ago 91
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( as members, COUNT( as projects,
o.status,o.organization_name,o.logo, as id
from tbl_organizations o
LEFT JOIN tbl_organization_members m ON ( = m.organization_id)
LEFT JOIN tbl_projects p ON ( = 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 Source

You can co-related subquery:

SELECT as Organization,
    (SELECT COUNT(*) FROM tbl_organization_members WHERE organization_id = as members,
    (SELECT COUNT(*) FROM tbl_projects WHERE organization_id = as projects
    tbl_organizations o
    o.status= 'active' AND o.created_by = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download