Conditional aggregation query giving result for individual record

Question

I have the following tables exactly as follows

Order

OrderID	UserID
1	7
2	1
3	13
4	7

RentalItems

RentalItemsID	ItemID	ReturnTypeID	OrderID
1	9	5	1
2	3	4	2
3	4	5	2
4	5	5	2
5	10	4	3
6	11	4	4
7	14	5	4

ReturnTypeID 5: Defect Return

ReturnTypeID 4: Normal Return

select [order].UserID,
    (
        select avg(case when ReturnTypeID = 5 then 1.0 else 0 end) as DefectedRate
        from RentalItems 
        where RentalItems.OrderID = [Order].OrderID 
    ) as DefectedReturnRate
from [Order] 
where OrderID in (select OrderID from RentalItems where ReturnTypeID=5)

Current Output: Calculated as: DefetedReturnByUserPerOrder/AllReturnsByUserPerOrder

UserID	DefectReturnRate	Showing Calculation, (Not incl. in output)
7	1.00	Order1: 1/1 - Contained 1 item and it was defected returned
1	0.66	Order2: 2/3 - Contained 3 items and 2 were defected returned
7	0.50	Order4: 1/2 - Contained 2 items and 1 was defected returned

as you can see, it is calculating DefetecedReturnRate per each Order and not total Items rented by user across ALL Orders

Desired Output: TotalDefetedReturnByUserForALLOrders/AllReturnsByUserForAllOrders

UserID	DefectReturnRate	Showing Calculation, (Not incl. in output)
7	0.66	Order1&4: 2/3 - Total Rented items by User 7 were 3 and 2 were defected Returns
1	0.66	Order2: 2/3 - Contained 3 items and 2 were defected returned

I think this will be my last question so any help will be greatly appreciated. I can provide DML+DDL data but I'm not sure how to. I tried to generate scripts but it was only generating tables and not data. but the tables and data is exactly as I have entered in tables above.

You create DDL+DML by *writing* create temp table statements (or table variable statements) and *writing* insert statements with a small sample of data :) You can see it on many questions and answers on this site. [Example](https://stackoverflow.com/a/66579175/1127428) — Dale K, Mar 15 '21 at 19:13
Ohh.. I see. I thought it was something that can be generated. Thank you!. — Nebelz Cheez, Mar 15 '21 at 19:47
You can generate it from SSMS https://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table — Charlieface, Mar 15 '21 at 21:42

score 1 · Accepted Answer · edited Mar 15 '21 at 19:13

1

Here is how you can do it:

SELECT UserID, AVG(case when ReturnTypeID = 5 then 1.0 else 0 end) as DefectedRate
FROM orders 
JOIN dbo.RentalItems AS ri
   ON ri.OrderID = orders.OrderID
WHERE ri.OrderID in (select OrderID from RentalItems where ReturnTypeID = 5)
GROUP BY UserID

edited Mar 15 '21 at 19:13

Dale K

25,246
15
42
71

answered Mar 15 '21 at 19:00

eshirvana

23,227
3
22
38

Ahh.. I see what you did. Thank you!! – Nebelz Cheez Mar 15 '21 at 19:05

score 1 · Answer 2 · answered Mar 15 '21 at 19:30

You can also write it slightly differently without the need for hitting RentalItems twice.

This way yields 5 logical reads vs 12 logical reads, so in the real world will be more performant, should that matter for your circumstances.

select distinct userid, Returned
from (
    select o.userid,  Sum (case when ReturnTypeId=5 then 1.0 end) over( partition by userid) / Count(*) over(partition by userid)  Returned
    from Orders o
    join RentalItems ri on ri.orderid=o.orderid
)s
where returned is not null

it does not matter in my case but I see your approach aswell. I wish I could select both answers as reply but thank you! — Nebelz Cheez, Mar 15 '21 at 19:52

Conditional aggregation query giving result for individual record

2 Answers2