Conditional Aggregation with multiple case and group by

Question

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

I tried group by but it was not working as expected. Sorry for the delay, I was trying to figure out how to generate scripts with data. Currently only letting me generate table scripts but no data.. — Nebelz Cheez
– Nebelz Cheez, Commented Mar 14, 2021 at 23:51
You just have to type in what you have shown above... rather than us typing it in (since its you that wants assistance) :) — Dale K
– Dale K, Commented Mar 14, 2021 at 23:52
Right, sorry about that Dale, I will keep in mind next time :) — Nebelz Cheez
– Nebelz Cheez, Commented Mar 15, 2021 at 0:10

Dale K · Accepted Answer · 2021-03-14 23:53:26Z

1

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.

answered Mar 14, 2021 at 23:53

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nebelz Cheez Over a year ago

Thanks Dale, this Worked as expected! :)

Stu · Accepted Answer · 2021-03-15 00:15:08Z

1

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

edited Mar 15, 2021 at 0:15

answered Mar 14, 2021 at 23:50

Stu

32.7k6 gold badges17 silver badges34 bronze badges

3 Comments

Nebelz Cheez Over a year ago

Hey this worked but when incorporating in my query I got error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. But thank you for your answer, I see what you did

Charlieface Over a year ago

@NebelzCheez Without a subquery, in the main part

Stu Over a year ago

Ah ok. The perils of not acutally having the data to play with ;-) This is because of the partition it's now returning a row for each addressId. it needs additional correlation. I'll edit above

Collectives™ on Stack Overflow

Conditional Aggregation with multiple case and group by

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related