CODEWITHSUNDEEP
sqloracle-database
Kaiser Wilhelm
Kaiser Wilhelm

Reputation: 618

Reset sum when condition is met in Oracle

My data is structured as follows:

Timestamp   | Hour | Count
--------------------------
20190801 01 |    1 |    10
20190801 02 |    2 |    20
20190801 03 |    3 |    10
20190801 04 |    4 |     5
20190801 05 |    5 |    15
20190801 06 |    6 |    10
20190802 01 |    1 |     5
20190802 02 |    2 |    20
20190802 03 |    3 |     5
20190802 04 |    4 |    15
20190802 05 |    5 |    20
20190802 06 |    6 |     5
20190803 01 |    1 |    30

I'm trying to make an SQL query that will calculate a running SUM but resets when the hour is 3. The result should look like this:

Hour | Count | SUM
------------------
   1 |    10 |  10
   2 |    20 |  30
   3 |    10 |  10    /* RESET */
   4 |     5 |  15
   5 |    15 |  30
   6 |    10 |  40
   1 |     5 |  45    
   2 |    20 |  65
   3 |     5 |   5    /* RESET */
   4 |    15 |  20
   5 |    20 |  40
   6 |     5 |  45
   1 |    30 |  75

Upvotes: 3

Views: 247

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175766

You could create subgroup using conditional sum:

WITH cte AS (
   SELECT t.*,SUM(CASE WHEN hour=3 THEN 1 ELSE 0 END) OVER(ORDER BY timestamp) grp
   FROM t
)
SELECT cte.*, SUM(Count) OVER(PARTITION BY grp ORDER BY timestamp) AS total
FROM cte

Upvotes: 2

Related Questions