sql - Select all rows where the sum of column X is greather or equal than Y

Question

I need to find a group of lots to satisfy X demand for items. I can't do it with aggregate functions, it seems to me that I need something more than a window function, do you know anything that can help me solve this problem?

For example, if I have a demand for 1 Item, the query should return any lot with a quantity greater than or equal to 1. But if I have a demand for 15, there are no lots with that availability, so it should return a lot of 10 and another with 5 or one of 10 and two of 3, etc.

With a programming language like Java this is simple, but with SQL is it possible? I am trying to achieve it with sales functions but I cannot find a way to add the available quantity of the current row until reaching the required quantity.

SELECT  id,VC_NUMERO_LOTE,SF_FECHA_CREACION,SI_ID_M_ARTICULO,VI_CANTIDAD,NEXT, VI_CANTIDAD + NEXT AS TOT FROM (
SELECT row_number() over (ORDER BY SF_FECHA_CREACION desc) id ,VC_NUMERO_LOTE,SF_FECHA_CREACION,SI_ID_M_ARTICULO,
VI_CANTIDAD,LEAD(VI_CANTIDAD,1)  OVER (ORDER BY SF_FECHA_CREACION desc) as NEXT FROM PUBLIC.M_LOTE WHERE SI_ID_M_ARTICULO = 44974
AND  VI_CANTIDAD > 0 ) AS T
WHERE   MOD(id, 2) != 0

I tried with lead to then sum only odd records but I saw that it is not the way, any suggestions?

question from:https://stackoverflow.com/questions/65950237/select-all-rows-where-the-sum-of-column-x-is-greather-or-equal-than-y

Answer 1

You need a recursive query like this:

demo:db<>fiddle

WITH RECURSIVE lots_with_rowcount AS (       -- 1
    SELECT 
        *, 
        row_number() OVER (ORDER BY avail_qty DESC) as rowcnt
    FROM mytable
), lots AS (                                 -- 2
    SELECT                                   -- 3
        lot_nr,
        avail_qty,
        rowcnt,
        avail_qty as total_qty
    FROM lots_with_rowcount
    WHERE rowcnt = 1
    
    UNION
    
    SELECT 
        t.lot_nr,
        t.avail_qty,
        t.rowcnt,
        l.total_qty + t.avail_qty           -- 4
    FROM lots_with_rowcount t
    JOIN lots l ON t.rowcnt = l.rowcnt + 1
        AND l.total_qty < --<your demand here>
)
SELECT * FROM lots                          -- 5

1. This CTE is only to provide a row count to each record which can be used within the recursion to join the next records.
2. This is the recursive CTE. A recursive CTE contains two parts: The initial SELECT statement and the recursion.
3. Initial part: Queries the lot record with the highest avail_qty value. Naturally, you can order them in any order you like. Most qty first yield the smallest output.
4. After the UNION the recursion part: Here the current row is joined the previous output AND as an additional condition: Join only if the previous output doesn't fit your demand value. In that case, the next total_qty value is calculated using the previous and the current qty value.
5. Recursion end, when there's no record left which fits the join condition. Then you can SELECT the entire recursion output.

Notice: If your demand was higher than your all your available quantities in total, this would return the entire table because the recursion runs as long as the demanded is not reached or your table ends. You should add a query before, which checks this:

SELECT SUM(avail_qty) > demand FROM mytable