Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Query to count a column based on the previous row's column result

I have a table with this data:
enter image description here

create table my_table (
    month varchar(2),
    services integer null,
    s_registered integer,
    s_terminated integer
);

insert into my_table(month, services, s_registered, s_terminated) values
    ('01', 395, 14, 14),
    ('02', null, 5, 9),
    ('03', null, 19, 15),
    ('04', null, 30, 11),
    ('05', null, 13, 15),
    ('06', null, 8, 11),
    ('07', null, 10, 17),
    ('08', null, 13, 17),
    ('09', null, 5, 10),
    ('10', null, 10, 19),
    ('11', null, 7, 22),
    ('12', null, 18, 13);

You can see the services column and I need a query to select the result with the calculated services in each row.
The services column is equal to services + s_registered - s_termination of the previous row.enter image description here

I tried using the window cte LAG() function, but it only worked once. It correctly accepts the first services column, but on the next iteration the LAG takes the column as null.

I think this issue can be resolved with recursive CTE, do you have any ideas?:)

Tags:

2

Answers


  1. 0

    You can indeed do this with a recursive CTE. For example, you could use this as a base and customise it for your needs:

    WITH RECURSIVE cte AS (
    SELECT * FROM mytable where month = 1
    UNION ALL
    SELECT
        mytable.month,
        cte.services + cte.s_registered - cte.s_terminated,
        mytable.s_registered,
        mytable.s_terminated
    FROM cte
    INNER JOIN mytable ON cte.month + 1 = mytable.month
)
SELECT * FROM cte;
    Login or Signup to reply.
  2. 0

    You do not want a recursive cte for this, you can simply use the SUM window function:

    select
    month,
    sum(coalesce(services,0) + s_registered - s_terminated) over (order by month) services,
    s_registered,
    s_terminated
from my_table
order by month;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search