Reputation: 5001
The query below:
select
yu.Soyad,
mt.ICD10Kodu,
count(mi.ID) as AASayisi
from
Muayene.Muayene mm
join Muayene.Ilac mi
on mm.ID = mi.MuayeneId
join Yetkilendirme.Users yu
on mi.CreatedBy = yu.ID
join Muayene.Tani mt
on mm.ID = mt.MuayeneId
where
mm.IsDeleted <> 1
and mi.IsDeleted <> 1
and mt.IsDeleted <> 1
group by
yu.Soyad,
mt.ICD10Kodu
returns the data below:
HALAÇ J00 5
DEĞİRMENCİOĞLU J00 12
GÖKALP J02 22
DİKİLİLER J03 14
but actually I need the rate of the entity not the exact number it occurs so I need to divide it like this:
select
yu.Soyad,
mt.ICD10Kodu,
count(mi.ID)/count(select * from Muayene.Muayene where ID=mm.ID) as AASayisi
from
Muayene.Muayene mm
join Muayene.Ilac mi
on mm.ID = mi.MuayeneId...
but it doesn't work. It gives the error message:
the multipart identifier mi.ID couldn't be found
How can I make it work?
Upvotes: 1
Views: 189
Reputation: 13146
You can't do it like this because you are not grouping mm.ID
field. You should calculate the counts in seperated queries and then you can join them to calculate ratio;
select yu.Soyad, mt.ICD10Kodu, AASayisi, MMSayisi, (cast(AASayisi as float)/cast(MMSayisi as float)) As Ratio from (
select yu.ID as YUID, yu.Soyad, mt.ICD10Kodu, count(mi.ID) as AASayisi
from Muayene.Muayene mm
join Muayene.Ilac mi on mm.ID = mi.MuayeneId
join Yetkilendirme.Users yu on mi.CreatedBy = yu.ID
join Muayene.Tani mt on mm.ID = mt.MuayeneId
where mm.IsDeleted <> 1 and mi.IsDeleted <> 1 and mt.IsDeleted <> 1
group by yu.ID,yu.Soyad, mt.ICD10Kodu) miCountsQuery inner join (select yu.ID as YUID,yu.Soyad, count(mm.ID) as MMSayisi
from Muayene.Muayene mm
join Yetkilendirme.Users yu on mm.CreatedBy = yu.ID
where mm.IsDeleted <> 1
group by yu.ID,yu.Soyad) mmCountsQuery ON miCountsQuery.YUID = mmCountsQuery.YUID
The query wasn't tested. Some modifications might be needed.
Upvotes: 1
Reputation: 1221
You need to do your COUNT
operations in joined queries, not a query inside your SELECT
list. Queries in SELECT
lists don't work unless you're pulling exactly 1 value, which you are not doing here. Also, you can't do mathematical operations on derived values like COUNT()
in the same query in which you're deriving them.
So to calculate your rate you can do the below and then join it to whatever else you need using ID
select
a.mi_ID as ID
, a.ct_mi / b.ct_mm as Rate
from
(select
mi.ID as mi_ID
, count(mi.ID) as ct_mi
from [Muayene.Ilac] as mi
group by mi.ID) as a
inner join
(select
mm.ID as mm_ID
, count(mm.ID) as ct_mm
from [Muayene.Muayene] as mm
group by mm.ID) as b
on a.mi_ID = b.mm_ID
Upvotes: 0