Ver 1:
Select
p.facnum,
count(distinct case when m.contr = 3 then p.resnum else NULL end),
count(distinct p.resnum)
from patients p, patients_meds pm, meds m
where p.resnum=pm.resnum and pm.pmcode=m.pmcode
group by p.facnum
Ver 2:
Select
p.facnum,
sum(case when exists (
Select *
from patients_meds pm
inner join meds m on pm.pmcode=m.pmcode
where p.resnum=pm.resnum and m.contr = 3) then 1 else 0 end),
count(p.resnum)
from patients p
group by p.facnum
sum(case when m.contr = 3 then 1 else 0 end)That won't give the right result because it will count the same person multiple times due to the cartesian product from joining across the 3 tables.
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
mysql date time | 14 | 30 | |
TSQL - How to declare table name | 26 | 30 | |
Find results from sql within a time span | 11 | 31 | |
error in my cursor | 5 | 32 |
Join the community of 500,000 technology professionals and ask your questions.