Query for grouping by comparing sum of value for group

Question

Is there a way using SQL to group data such that you're comparing the sum of a particular value against different values for each group?

For example, consider the following schema:

Table: ITEM
Columns:
    ITEM_NUMBER (VARCHAR(15))
    QUANTITY_ON_HAND (DECIMAL(5,2))
Primary Key: ITEM_NUMBER
Table: ORDER_ITEM_REQUIREMENTS
Columns:
    ITEM_NUMBER (VARCHAR(15), Foreign Key to ITEM)
    DATE_ORDERED (TIMESTAMP)
    QUANTITY (DECIMAL(4,2))
    COST_PER (DECIMAL(10,2))
Primary Key: ITEM_NUMBER+DATE

Request: For each Item Number, order the Order Item Requirements in ascending order by Date, and get the maximum amount such that the sum of the Quantities is <= the Quantity On Hand for that Item Number (get all Order Item Requirements if the sum is less than the Quantity On Hand). Then output a result containing ItemNumber, NumShipments, QtySum, and CostSum (which is the sum of the product of CostPer and Quantity).

If it matters, we're using IBM DB2 Version 7 Revision 3.

For example, given the following data:

{ITEM_NUMBER: "APPLE", QUANTITY_ON_HAND: 5}
{ITEM_NUMBER: "APPLE", DATE_ORDERED: Jan 1, 12:01, QUANTITY: 2, COST_PER: 1.23}
{ITEM_NUMBER: "APPLE", DATE_ORDERED: Jan 1, 12:02, QUANTITY: 2, COST_PER: 2.34}
{ITEM_NUMBER: "APPLE", DATE_ORDERED: Jan 2, 12:03, QUANTITY: 2, COST_PER: 5.55}

Or in terms of SQL:

CREATE TABLE MYLIB.ITEM (ITEM_NUMBER VARCHAR(15) NOT NULL NOT HIDDEN , QUANTITY_ON_HAND DECIMAL(5, 2) NOT HIDDEN , PRIMARY KEY (ITEM_NUMBER)  ) NOT VOLATILE UNIT ANY KEEP IN MEMORY NO ;
CREATE TABLE MYLIB.ORDER_ITEM_REQUIREMENTS (ITEM_NUMBER VARCHAR(15) NOT NULL NOT HIDDEN , DATE_ORDERED TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL NOT HIDDEN , QUANTITY DECIMAL(4, 2) NOT NULL NOT HIDDEN , COST_PER DECIMAL(10, 2) NOT NULL NOT HIDDEN , PRIMARY KEY (DATE_ORDERED, ITEM_NUMBER) , FOREIGN KEY (ITEM_NUMBER) REFERENCES MYLIB.ITEM (ITEM_NUMBER) ON DELETE NO ACTION ON UPDATE NO ACTION  ) NOT VOLATILE UNIT ANY KEEP IN MEMORY NO ;
INSERT INTO MYLIB.ITEM (ITEM_NUMBER, QUANTITY_ON_HAND) VALUES ('APPLE', 5);
INSERT INTO MYLIB.ORDER_ITEM_REQUIREMENTS (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 2, 1.23);
INSERT INTO MYLIB.ORDER_ITEM_REQUIREMENTS (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 2, 2.34);
INSERT INTO MYLIB.ORDER_ITEM_REQUIREMENTS (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 2, 5.55);

I would like the following result:

{ITEM_NUMBER: "APPLE", NUM_SHIPMENTS: 2, QTY_SUM: 4, COST_SUM: 7.14}

score 2 · Accepted Answer · answered Aug 02 '23 at 18:30

One way of doing this would be

with t( item_number, num_shipments, qty_sum, cost_sum, qty_on_hand) as (
  select 
   i.item_number
   ,sum(1) over (
    partition by o.item_number order by o.date_ordered
   )
   ,sum(o.quantity) over (
    partition by o.item_number order by o.date_ordered
   )
   ,sum(o.quantity*o.cost_per) over (
    partition by o.item_number order by o.date_ordered
   ) 
  , i.quantity_on_hand
  from 
    item i 
  inner join
    order_item_requirements o
     on i.item_number = o.item_number
)
select 
  item_number
  ,max(num_shipments) num_shipments
  ,max(qty_sum) qty_sum
  ,max(cost_sum) cost_sum
from t where qty_sum <= qty_on_hand
group by item_number

I don't have access to IBM i, so this was tested on Db2 for LUW, but it should work.

Fiddle

Query for grouping by comparing sum of value for group

1 Answers1

Linked