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

From Andrus Moor
Subject Re: How to distribute quantity if same product is in multiple rows
Date
Msg-id FE7C1097D95B4CC1A1491B57279F5E6B@andrusnotebook
Whole thread Raw
In response to How to distribute quantity if same product is in multiple rows  ("Andrus" <kobruleht2@hot.ee>)
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

pgsql-general by date:

Previous
From: Samuel Gilbert
Date:
Subject: Blocked inserts on tables with FK to tables for which UPDATE has been revoked
Next
From: Vick Khera
Date:
Subject: Re: How to improve performance in reporting database?