MySQL: start group by after a value is reached -

i'm trying group column 'new_treads' @ value 'response' first equals 1.

id   |treads    |response ------------------ 1    | 1        | 0         2    | 1        | 1 3    | 2        | 0     4    | 2        | 0 5    | 2        | 1 6    | 2        | 1 7    | 2        | 1 ...  | ...      | ... 15   | 9        | 0      16   | 9        | 1     17   | 9        | 0   18   | 9        | 1  19   | 10       | 0   

currently can use:

select  thread, count(response), sum(response) messages group thread 

to get:

treads    |count(response) | sum(response) ------------------------------------------  1        | 2              | 1  2        | 5              | 3  9        | 4              | 2   10       | 1              | 0 

but want start group by, or count(response), 'response' = 1. giving:

treads    |count(response) | sum(response) ------------------------------------------  1        | 1              | 1  2        | 3              | 3  9        | 3              | 2   10       | 0              | 0 

thanks!

this messy, sorry.

select treads, if(sum(response) >= 1, count(response), 0) count, sum(response) sum   messages     id not in (      select m.id         messages m         id <          (select min(id) id            messages            response = 1              , treads = m.treads          group treads)     )     group treads; 

fiddle here: http://sqlfiddle.com/#!9/fffd0/14

by way of explanation first subquery gets message ids id less id of first row tread response = 1 - value comes innermost subquery, , gives exclusion list filter out results of outermost query.

we use if statement inside count handle case record tread has response = 0, want included in results, not counted.