Oracle sum most recent records until a defined value then Ignore the rest-CodePudding

Im looking to sum a column until a defined value then ignore the rest of the records.

ID	WHEN	VALUE	AVG_COL
101	2016	6	84.5
101	2015	3	76
101	2014	3	87
101	2013	15	85.8
101	2012	6	92
101	2011	3	81
101	2010	3	82.3

I need a single result set of

ID	VALUE	AVG_COL
101	30	82.3

I have tried the following

SELECT
   ID,
   WHEN,
   VALUE,
   AVG_COL,
   SUM(VALUE) OVER (PARTITION BY ID ORDER BY WHEN) AS VALUE, --must equal 30
   AVG(AVG_COL) OVER (PARTITION BY ID) AVG
FROM
    TABLE_ONE
WHERE
   VALUE = 30;

Any help would be greatly appreciated!

CodePudding user response：

Hi try some thing like this, where modified the WHERE clause

-- Untested

SELECT
   ID,
   WHEN,
   VALUE,
   AVG_COL,
   SUM(VALUE) OVER (PARTITION BY ID ORDER BY WHEN) AS VALUE, --must equal 30
   AVG(AVG_COL) OVER (PARTITION BY ID) AVG
FROM
    TABLE_ONE
WHERE
   ID IN (SELECT ID FROM ( (SELECT ID, sum(VALUE) sum_val FROM 
           TABLE_ONE GROUP BY ID) WHERE SUM_VAL = 30);

CodePudding user response：

try this

select id,
SUM(VALUE) OVER (PARTITION BY ID ORDER BY WHEN RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS VALUE,
AVG(AVG_COL) OVER (PARTITION BY ID) AVG
from table_one
where VALUE <= 30
order by when desc
fetch first 1 rows only;