How to sum a column over a subset of rows in MYSQL

Question

i hope i can describe my problem in an understandable way.

So i got a table of logging events like so:

id	event	count	marker
1	EVENT 1	3	false
2	EVENT 1	2	false
3	EVENT 2	4	false
4	EVENT 1	1	true
5	EVENT 1	6	false
6	EVENT 1	2	true
7	EVENT 1	5	false
8	EVENT 2	3	true
9	EVENT 1	3	false
10	EVENT 2	5	false

What i want is the sum of the column 'count' of all rows newer (higher id) than the last occurence of a true in the column marker distinct for every event.

I was able to get that result for each event individually like so (example for 'event 1'):

select sum(count) from test_table
    where event='EVENT 1'
    and id > (select id from test_table 
    where event='EVENT 1' 
    and marker=1
    order by id desc limit 1);

The result for this query would be 8 (sum of lines 7 and 9), 5 for the query for 'EVENT 2' respectively (sum of the line 10).

But now I want to get these results in one query like so:

event	sum
EVENT 1	8
EVENT 2	5

Is that possible? I played around with window functions a bit, but didn't get the desired result.

I hope, someone can help me.

Greetings Frank

Answer 1

What i want is the sum of the column 'count' of all rows newer (higher id) than the last occurence of a true in the column marker distinct for every event.

One method is:

select event, sum(count)
from t
where id > (select max(t2.id) from t t2 where t2.event = t.event and marker)
group by event