ORACLE SQL : SUM in first row with condition

user_7589182 0
Viewed 63

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

Here is my table

Line_num Line_typ Cost
1000 6 0
2000 7 5000
3000 7 3000
4000 7 2000
5000 6 0
6000 9 3000
7000 7 2000
8000 1 2000

What I want as result is this

Line_num Line_typ Cost
1000 6 10000 (0+5000+3000+2000)
5000 6 5000 (0+3000+2000)
8000 1 2000

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

oracle sql
3 Answers

This is a play on a gaps and islands problem. Each island begins upon encountering a Line_typ value of 1 or 6, and ends right before a record containing another 1 or 6 value. We can use COUNT() as an analytic function to find the groups, then report the sums of cost.

WITH cte AS (
    SELECT t.*, CASE WHEN Line_typ IN (1, 6) THEN 1 ELSE 0 END AS flag
    FROM yourTable t
),
cte2 AS (
    SELECT t.*, SUM(flag) OVER (ORDER BY Line_num) AS grp
    FROM cte t
),
cte3 AS (
    SELECT t.*, MIN(Line_num) OVER (PARTITION BY grp) AS Min_Line_num
    FROM cte2 t
)

SELECT MIN(Line_num) AS Line_num,
       MAX(CASE WHEN Line_num = Min_Line_num THEN Line_typ END) AS Line_typ,
       SUM(Cost) AS Cost
FROM cte3 t
GROUP BY grp
ORDER BY MIN(t.Line_num);

screen capture from demo link below

Demo

user_18632290

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.

user_9848230

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

user_15092640
Related