Conditional Aggregation with multiple case and group by

The query below gives me average of case when QuoteStatusID = 6 but it I am having issues with associating the average by Street column.

QuoteTable

QuoteID	QuoteStateID	ProjectManager_userID	Shipping_AddressID
1	6	12	56
2	6	12	56
3	26	12	56
4	6	12	18
5	26	12	18

Shipping_AddressID

56: 338 Elizabeth St
18: 83 East St

select [User].UserID, [User].fname, [User].lname,[User].JobTitle, address.Street,
    (select avg(case when QuoteStatusID = 6 then 1.0 else 0 end) as QuoteAccept
    from Quote q
    where ProjectManager_UserID = userId
    ) as AcceptanceRate
from [User] 
join quote on [user].UserID=Quote.ProjectManager_UserID
join Address on quote.Shipping_AddressID=Address.AddressID
where userID in (select distinct ProjectManager_UserID from quote)
order by AcceptanceRate desc;

Current output 3/5 =0.60

userid	fname	Lname	Street	AcceptanceRate
12	Jon	Smith	338 Elizabeth St	0.6
12	Jon	Smith	83 East St	0.6

Desired output 2/3=0.66 AND 1/2=0.50

userid	fname	Lname	Street	AcceptanceRate
12	Jon	Smith	338 Elizabeth St	0.66
12	Jon	Smith	83 East St.	0.50

Answer

I think you don’t need a sub-query. Just avg as part of the query you have and use group by to give you distinct users and addresses.

select U.UserID, U.fname, U.lname, U.JobTitle, A.Street
    , avg(case when Q1.QuoteStatusID = 6 then 1.0 else 0 end) as QuoteAccept
from [User] U 
inner join Quote Q on Q.ProjectManager_UserID = U.UserID
inner join [Address] A on A.AddressID = Q.Shipping_AddressID
group by U.UserID, U.fname, U.lname, U.JobTitle, A.Street
order by AcceptanceRate desc;

Note: Short aliases make a query more readable. And you don’t need your where clause, since the join on Quote already ensures the same condition.