SQL : get count of data which have both mutations

Question

I have 2 tables Mutations and Medications.

I need the count of those members who have both mut1 and mut2 within a med name

SELECT 
    med.Name AS medicine, COUNT(*) AS count
FROM 
    Mutations mut
INNER JOIN 
    Medications med ON med.MemberId = mut.MemberId
WHERE 
    mut.Mutation IN ('mut1','mut2')
GROUP BY 
    med.Name
HAVING 
    COUNT(DISTINCT mut.Mutation) = 2

How to get count of those members who have both mutation?

Answer 1

One method is two levels of aggregation:

select med.name, count(*)
from (select med.name, m.memberid
      from medications med join
           mutations m
           on m.memberid = med.memberid
      where m.mutation in ('mut1', 'mut2')
      group by med.name, m.memberid
      having count(*) = 2
     ) m
group by med.name;