Finding Cumulative Sum of column with string data type-CodePudding

I need to calculate the cumulative sum for the tickets which are only open. I have a table with id, open_date, ticket_status, and ticket_closed. I'm not sure how to calculate the cumulative sum only for the open tickets with the data type being string.

I have a table tb with the following structure:

id	open_date	ticket_status	ticket_closed
1	01-01-2022	open
2	01-01-2022	closed	01-02-2022
3	01-01-2022	open
4	01-02-2022	open
5	01-03-2022	open

I want output to be the following

id	open_date	ticket_status	ticket_closed	cumulative_sum
1	01-01-2022	open		1
2	01-01-2022	closed	01-02-2022
3	01-01-2022	open		2(1 1)
4	01-02-2022	open		3(2 1)
5	01-03-2022	open		4(2 1)

I have tried the following code and it's not giving me the output I'm expecting

SELECT id, open_date,
       SUM(CASE WHEN 'ticket_status' = 'open' THEN 1 ELSE NULL END) OVER (ORDER BY open_date ASC ROWS UNBOUNDED PRECEDING)
FROM tb

any help would be appreciated!

CodePudding user response：

Try

SUM(CASE WHEN 'ticket_status' = 'open' THEN 1 ELSE 0 END) OVER (ORDER BY open_date, id)

CodePudding user response：

It looks like your "id" field identifies the order of insertion for your records. If that's the case, you can use it inside the ORDER BY clause of your COUNT window function. Then update your field value only when your ticket_status='open'.

SELECT id, open_date,
       CASE WHEN ticket_status = 'open'
            THEN COUNT(CASE WHEN ticket_status = 'open' THEN 1 END) OVER (ORDER BY id)
       END
FROM tb

Here's a demo in MySQL, although this query is likely to work on all the most common DBMS'.