I am trying to find average using conditional aggregation. I have 2 tables in SQL as below
RentalExtension Table:
RentalItemsID | ExtensionDate | ExtensionBy_UserID |
---|---|---|
7 | 2020-07-27 | 10 |
1 | 2020-07-28 | 7 |
The user table contains attributes as listed in select query
select UserID, fname, lname, JobTitle,
(
select avg(case when ExtensionBy_UserID = 10 then 1.0 else 0 end) as OrderExtended
from RentalExtensions
where ExtensionBy_UserID=userID
) as ExtensionRate
from [user]
where userID = 10
When I run the query above I get Extension Rate for UserID=10 as 1.00 and UserID=7 as 0.00. This is not correct output. When I run the query below, it gives correct output of 0.500 for userID=10 and that is what I want the top query to show. If I can get the averages for both users, that would be even better.
select avg(case when ExtensionBy_UserID = 10 then 1.0 else 0 end) as OrderExtended
from RentalExtensions
Any help with where I am going wrong?
Advertisement
Answer
@GordonLinoff nearly had it right.
You cannot use an outer reference directly inside a nested query’s aggregation function (for reasons that are unclear to me).
So you need to get that outer reference into the body of the inner query.
We can do this with CROSS APPLY (VALUES(
select
UserID,
fname,
lname,
JobTitle,
(select
avg(case when re.ExtensionBy_UserID = v.userId then 1.0 else 0 end)
from RentalExtensions re
cross apply (values (u.userId) ) v(userId)
) as ExtensionRate
from [user]
where userID =10
EDIT: You say in a comment that you actually want the whole user
table.
So it’s better to just use a normal join
/group by
setup, with a window aggregate:
select
UserID,
fname,
lname,
JobTitle,
count(*) as CountOfExtenstions,
count(*) * 1.0 / count(count(*)) over () as ExtensionRate
from [user]
join RentalExtensions re on re.ExtensionBy_UserID = v.userId
group by userID;