Best way to filter to rows with partitioned SUM

Question

I have a table that consists of an order, items on that order, and then the quantity of the item ordered.

What I would like to do is create an additional column for 'Order quantity' which is the sum of item quantities grouped by order (see graphic of table below... order B has 30 total quantity split across three lines)

I can easily accomplish this using sum and partition:

SUM(quantity) OVER (PARTITION BY order_id) order_qty

However, what I need to is then filter to only those orders having quantity > 20. When I try to add that criteria to the WHERE or HAVING clauses, I get this error:

ORA-30483: window  functions are not allowed here

One solution appears to be to wrap the whole SQL block inside of another SELECT FROM statement, and then add a WHERE clause to filter by order_qty. Overall that seems sloppy and non-intuitive... Is there a better solution to filter based on an aggregated value that is partitioned at a higher level?

Maybe nothing. I'm just asking if there is a more efficient way. Plus subselect reduces code legibility imo. — corycorycory, Dec 20 '17 at 20:41
This is just a simplified example as the actual query is much more complex... select order_id, item_id, quantity, SUM(quantity) OVER (PARTITION BY order_id) order_qty FROM orders HAVING SUM(quantity) OVER (PARTITION BY order_id) order_qty > 20 — corycorycory, Dec 20 '17 at 20:55

score 1 · Answer 1 · answered Dec 20 '17 at 20:42

1

Replace with

SUM(quantity) OVER (PARTITION BY order_id order by 1) order_qty

answered Dec 20 '17 at 20:42

ibrahim

63
6

This yields the same error. ORA-30483: window functions are not allowed here – corycorycory Dec 20 '17 at 20:44
Window functions are allowed only in the SELECT list of a query. A window function cannot be a group function. – ibrahim Dec 20 '17 at 20:59
Are you use group by ? – ibrahim Dec 20 '17 at 20:59
@ibrahim yes, your solution is good : `select o.*, SUM(quantity) OVER (PARTITION BY order_id order by 1) order_qty from orders o order by order_id, item_id;` or subselect with grouping by order_id maybe prefered. – Barbaros Özhan Dec 20 '17 at 21:21

Best way to filter to rows with partitioned SUM

1 Answers1