I'm struggling to obtain the following results in a query

Here is my table

What I want as result is this

Basically to display only rows with line_typ in (6,1) but sum the column costs of all other lines in between.

Thank you for your ideas and help !!

Ivan

CodePudding user response：

The reason Tim Biegeleisen's answer is excellent, but needs some intermediate products and oracle features, can be seen when one uses minimal SQL:

• Select line_typ in (1, 6), order by line_num.
• Sum the cost for second run over the table with larger line_num n, but
• where (third run) there is no line_typ in (1, 6) before n.

Basic complexity O(N³), with indices a bit less.

So:

select h.line_num, h.line_typ,
 (select sum(cost)
  from tbl g
  where g.line_num >= h.line_num
  and not exists (select *
                  from tbl
                  where line_num > h.line_num
                  and line_typ in (1, 6)
                  and line_num <= g.line_num)
 ) as cost
from tbl h
where h.line_typ in (1, 6)
order by h.line_num

(Given for non-oracle searchers.)

Here is a link to a working

Demo

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row pattern matching:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY line_num
  MEASURES
    FIRST(line_num) AS line_num,
    FIRST(line_typ) AS line_typ,
    SUM(cost)      AS total_cost
  PATTERN (match_type other_type*)
  DEFINE
    match_type AS line_typ IN (6, 1),
    other_type AS line_typ NOT IN (6, 1)
)

Which, for the sample data:

CREATE TABLE table_name (line_num, line_typ, cost) AS
  SELECT 1000, 6,    0 FROM DUAL UNION ALL
  SELECT 2000, 7, 5000 FROM DUAL UNION ALL
  SELECT 3000, 7, 3000 FROM DUAL UNION ALL
  SELECT 4000, 7, 2000 FROM DUAL UNION ALL
  SELECT 5000, 6,    0 FROM DUAL UNION ALL
  SELECT 6000, 9, 3000 FROM DUAL UNION ALL
  SELECT 7000, 7, 2000 FROM DUAL UNION ALL
  SELECT 8000, 1, 2000 FROM DUAL;

Outputs:

LINE_NUM	LINE_TYP	TOTAL_COST
1000	6	10000
5000	6	5000
8000	1	2000

db<>fiddle here