CODEWITHSUNDEEP
oracle-databaseplsql
Kumar
Kumar

Reputation: 15

Aggregating values based on condition

Below is a table using which I am trying to establish a table of content shown in table B.

Table A

Sno. Total Value
1 0 1
2 225 0
3 210 2
4 0 0
5 0 0.5
6 150 0
7 150 0.5

Table B

Total Value
435 3
300 1.0

I am trying to accumulate and get a result of Total and Value until both are defined (i.e. Row-3, Row 7) using SELECT statement. I have tried to use LEAD and LAG to check if both values are defined in table but its not working for me.

Upvotes: 0

Views: 43

Answers (1)

MT0
MT0

Reputation: 168051

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

SELECT *
FROM   A
MATCH_RECOGNIZE(
  ORDER BY sno
  MEASURES
    SUM(total) AS total,
    SUM(value) AS value
  PATTERN (undefined* defined)
  DEFINE
    undefined AS total = 0 OR value = 0,
    defined   AS total > 0 AND value > 0
)

Which, for the sample data:

CREATE TABLE A (Sno, Total, Value) AS
SELECT 1,   0, 1   FROM DUAL UNION ALL
SELECT 2, 225, 0   FROM DUAL UNION ALL
SELECT 3, 210, 2   FROM DUAL UNION ALL
SELECT 4,   0, 0   FROM DUAL UNION ALL
SELECT 5,   0, 0.5 FROM DUAL UNION ALL
SELECT 6, 150, 0   FROM DUAL UNION ALL
SELECT 7, 150, 0.5 FROM DUAL;

Outputs:

TOTAL VALUE
435 3
300 1

fiddle

In earlier versions, you can use analytic functions and then aggregate:

SELECT SUM(total) AS total,
       SUM(value) AS value
FROM   (
  SELECT total,
         value,
         COUNT(CASE WHEN total = 0 AND value = 0 THEN 1 END)
           OVER (ORDER BY sno) AS grp
  FROM   A
)
GROUP BY grp;

Which outputs the same.

fiddle

Upvotes: 1

Related Questions