Home > Software engineering >  Limit result depending on sum of value
Limit result depending on sum of value

Time:10-28

I´ve been trying to automize a list of addresses in SQL. I have multiple addresses and quantities and i need only the addresses that will fulfill the quantity i need

For example:

I have a table with

Item A qty 20
Item B qty 5
Item C qty 23

And a table with addresses and units

Address 1  item A  15units
Address 2  item A  10units
Address 3  item A  10units
Address 4  item A  13units

The result should show only

Address 2  item A  10units
Address 3  item A  10units

CodePudding user response:

Assuming that your first table sales stores the sales made :

Item qty
A 20
B 5
C 23

and your second table stocks indicates the addresses of the stored items :

Address item units
1 A 15
2 A 10
3 A 10
4 A 13

then you can select a subset of addresses so that the sum of the stored units is equal or greater than the quantity sold :

WITH list AS (
SELECT st.item
     , sa.qty AS sales_qty
     , array_agg(st.address) OVER w AS addresses
     , array_agg(st.units) OVER w AS units
     , sum(units) OVER w AS total_stored_units
  FROM sales AS sa
 INNER JOIN stocks AS st
    ON st.item = sa.item
WINDOW w AS (PARTITION BY st.item ORDER BY st.address)
)
SELECT DISTINCT ON (item)
       item, unnest(addresses) AS address, unnest(units) AS units
  FROM list
 WHERE total_stored_units >= sales_qty
 ORDER BY item, total_stored_units ASC

Result

item address units
A 1 15
A 2 10

see dbfiddle

CodePudding user response:

Thanks a lot @Edouard! I ended up using something like

WITH r AS 
(
SELECT 
  item,
  address,
  quantity, 
  sum(quantity)
  OVER (
    PARTITION BY item
    ORDER BY quantity desc
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as total_units
FROM st
GROUP BY 1,2,3 
)

SELECT *
FROM r
GROUP BY 1,2,3,4
HAVING total_units <= qty_needed   avg(units)
ORDER BY units desc

I couldnt run your query in the bigquery console, but from it a re read some documentation of windows functions find this solution.

Again, thanks a lot!

  •  Tags:  
  • sql
  • Related