CODEWITHSUNDEEP
sqlsql-servert-sql
Nebelz Cheez
Nebelz Cheez

Reputation: 307

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

Upvotes: 1

Views: 179

Answers (2)

Stu
Stu

Reputation: 32599

Can you simply amend your avg to be

select avg(case when QuoteStateID = 6 then 1.0 else 0 end) over(partition by Shipping_AddressId) as QuoteAccept

Edit To still use as a subquery it will need correlating in the where clause on Shipping_AddressId also

Upvotes: 1

Dale K
Dale K

Reputation: 27201

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.

Upvotes: 1

Related Questions