Re: How to distribute quantity if same product is in multiple rows - Mailing list pgsql-general

Tim,

Thank you.

>>It can be done in SQL: "SUM(kogus) OVER (PARTITION BY toode
>>ORDER BY ID) - kogus" (*1) will give you the running sum of
>>the product up to that row. You can then subtract that value
>>from the delivered quantity to calculate the delivered quan-
>>tity for the current row.

I tried to get list of undelivered items using script below.
Second row value (22) is incorrect (it seems to be is cumulative sum but
must be undelivered quantity for this row).
How to fix this so that every row contains correct undelivered quantity ?

Andrus.

-- Order details
CREATE TEMP TABLE tellrid  (
id serial primary key,
toode char(20), -- product id
kogus numeric(12,5) ) -- ordered quantity
on commit drop;

insert into tellrid (toode,kogus) values ('PRODUCT1', 10 );
insert into tellrid (toode,kogus) values ('PRODUCT1', 20 );

-- Delivery details
CREATE TEMP TABLE rid  (
id serial primary key,
toode char(20), -- product id
kogus numeric(12,5) ) -- delivered quantity
on commit drop;

insert into rid (toode,kogus) values ('PRODUCT1', 8 );

select
  tellrid.id,
  max(tellrid.kogus) as ordered,

GREATEST( 0,

SUM(MAX(tellrid.kogus) )
       OVER (PARTITION BY MAX(tellrid.toode) ORDER BY tellrid.ID)

-  COALESCE( SUM(rid.kogus),0)

)   as not_delivered
from tellrid
LEFT JOIN rid USING (toode)
GROUP BY 1

Observed:

id  ordered     not_delivered
1  10.00000      2.00000
2  20.00000    22.00000


Expected:

id  ordered     not_delivered
1  10.00000      2.00000
2  20.00000    20.00000