CODEWITHSUNDEEP
mysqlsqlwindow-functions
Frank Murmann
Frank Murmann

Reputation: 71

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

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

Upvotes: 0

Views: 847

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

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

Upvotes: 4

Related Questions