Conditional Aggregation query not giving correct output

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?

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;