Returning rows based on sum of column

Question

I'm trying to retrieve a subset of data based on the sum of a column. The goal is a script to release backordered items. Say we have 100 BO products and get 50 in stock. I'd like to update the oldest orders where the sum of order qty < 50. So something like:

Sample Schema:

Orders Table:
order_id, order_date

order_products Table:
order_product_id, order_id, product_id, product_status, ordered_quantity


select * from products 
where products_id=1234 
and status=1 
and sum(products_qty) < 50;   

Where sum(products_qty) is the total returned qty, not just for the individual row. Not sure if this is possible with a single query or even a subquery, but thought I would ask the experts here first. I'm trying to avoid returning all the rows and then manually count up till I reach the limit.

Answer 1

You need use GROUP by and Having

select * from products 
where products_id=1234 
and status=1 
group by YourGroupingFields
Having sum(products_qty) < 50;   

based on your info:

select product_id from products 
where status=1 
group by product_id
Having sum(products_qty) < 50;   

will be return product_id for which total quantity less 50

Answer 2

When you're using aggregate functions like SUM() and COUNT(), you can't use them in WHERE clauses. WHERE clauses are applied row-by-row as the database scans the table/indexes, which means that the results of the aggregate functions aren't available yet.

Filtering by aggregate results has to be done using HAVING, which is essentially done as the last step before returning data to the client.

From the sounds of your requirement, you need a running count to be kept, until you've retrieved enough rows/orders to use up the new product being entered. This can't be done with a single query. You'd need to use a server-side variable to keep track of how much product's been "used up" by the individual backorders.

Going off the top of my head, something like this might do the trick:

SET @Available = 50;

SELECT order_id, SUM(ordered_quantity), @Available := @Available - SUM(ordered_quantity) AS available
FROM order_products
WHERE product_id = XXX
GROUP BY order_id, product_id
HAVING available >= 0;

with whatever extra WHERE clauses so you get the oldest backorders first