Mysql select unique columns from where clause-CodePudding

I'm trying to see if it's possible to have several unique columns in a select statement from varying where clause selections. Here is my query.

select org_id, count(org_role) as total
from organization
where org_id = 10 and org_role = 9
group by org_id;

the above works perfectly. It produces:

org_id   total

10        19

Id'd like to add another column count named total2 where org_id = 10 and org_role = 7 (the count is 23). So i'd have this result:

org_id    total    total2

10        19         23

I'm just not sure how to edit the original above query to produce that. Any thoughts would be appreciated.

CodePudding user response：

select org_id, count(org_role) as total, count(total_2)
from organization
where org_id = 10 and org_role = 9
group by org_id;

If it does not work please send your database structure.

CodePudding user response：

You can use conditional aggregation. For example:

select
  org_id,
  sum(case when org_role = 9 then 1 else 0 end) as total,
  sum(case when org_role = 7 then 1 else 0 end) as total2
from organization
where org_id = 10 and org_role in (7, 9)
group by org_id;