I am struggling in finding a solution to create a cumulative sum by group in SQL.

I have a table which have different events. What I want to accomplish is to sum the duration time of an event.

CREATE TABLE Events(
  EventID int,
  EventName varchar(20),
  EventTime numeric(17,6)
);

INSERT INTO Events VALUES (1, 'Event1_Start', 0.2);
INSERT INTO Events VALUES (2, 'Event2_Start', 0.3);
INSERT INTO Events VALUES (3, 'Event3_End', 0.2);
INSERT INTO Events VALUES (4, 'Event2_End', 0.6);
INSERT INTO Events VALUES (5, 'Event4_Start', 0);
INSERT INTO Events VALUES (2, 'Event2_Start', 0.3);
INSERT INTO Events VALUES (6, 'Event1_End', 0);

What I need is a cumulative sum that gives me the total duration of Event1 as column besides 'Event1_Start' row. I would like to sum all the intermediates events before my event_start and event_end.

I try to use sum over partition but no luck and CTE.

Thanks in advance for your suggestions. Best regards.

CodePudding user response：

you can group by part of the string:

select substr(eventname, 1, 6), sum(eventtime) from Events group by substr(eventname, 1,6)

db<>fiddle here

CodePudding user response：

SELECT EVENTID "EVENTID", EVENTNAME "EVENTNAME", EVENTTIME "EVENTTIME", CASE WHEN EVENTID = 1 THEN Sum(EVENTTIME) OVER() ELSE 0 END "CUMULATIVE_SUM_EVENT_1" FROM EVENTS