CODEWITHSUNDEEP
sqlsql-server
TyForHelpDude
TyForHelpDude

Reputation: 5001

sql inner join reference a table in subquery

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

Answers (2)

Emre Kabaoglu
Emre Kabaoglu

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

Rominus
Rominus

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

Related Questions