CODEWITHSUNDEEP
mysql
JMA
JMA

Reputation: 994

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?

Upvotes: 0

Views: 31

Answers (3)

juergen d
juergen d

Reputation: 204756

Group by the organisation columns and count distinct IDs

SELECT o.status,o.organization_name, o.logo, o.id as id,
       COUNT(distinct m.id) as members, COUNT(distinct p.id) as projects, 
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
GROUP BY o.status, o.organization_name, o.logo, o.id

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this way:

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

This way you JOIN to an already aggregated version of member/project tables, so as to get the count of members/projects per organization_id.

Upvotes: 1

Shaharyar
Shaharyar

Reputation: 12439

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

Upvotes: 0

Related Questions