CODEWITHSUNDEEP
phpmysqljoinsubquery
user3420034
user3420034

Reputation:

How to do a SUM subquery within a left join query?

So I have this data:

Table 'group_info':

identifier      name
123             group a
124             group b

Table: 'group_participants':

id              group_id       privilege
1               123            admin
2               123            user
3               124            admin

I want to get the number of people that are 'user' or 'admin' and the number of people that are 'admin', along with the group identifier and name, for all the groups that user id 1 is a member of.

e.g. in the case above this would return 123, 'group a', 2 users and 1 admin.

I'm trying to do this in a single query but can't quite get the final part. I have this so far:

SELECT group_info.identifier, group_info.name
FROM `group_info`
LEFT JOIN group_participants ON group_participants.group_id = group_info.identifier
WHERE group_participants.user_id = 1

I'm aware this can be done easily in 2 queries, but I'd rather do it all at once. Any ideas how to format the subquery to achieve this?

Upvotes: 1

Views: 192

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Your question is not clear on what "user or above" means. But the answer to your question is to use conditional aggregation along with a HAVING clause:

SELECT gi.identifier, gi.name,
       sum(gp.priviledge in ('user', 'admin')) as numUserOrAdmin,
       sum(gp.priviledge = 'admin') as numAdmins
FROM group_info gi LEFT JOIN
     group_participants gp
     ON gp.group_id = gi.identifier
GROUP BY gi.identifier, gi.name
HAVING SUM(gp.user_id = 1) > 0

Upvotes: 1

Related Questions