mysqlwhere-clauseclause

Mysql select unique columns from where clause

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.

Solution

  • 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;