mysql - Count Consecutive Numeric values in SQL

Question

There are already many existing questions about this scenario, however I am unable to replicate the answers in my scenario.

I have a following sample Data Set:

ID Number  | Values 
754321       0
754321       0
754321       0
754321       0
754321       1
754321       0
754321       1
754321       0
754321       2
754321       0
754329       3
754329       4
754329       5
754329       6
754329       7
754329       8
754329       9 

I want the SQL query that outputs the ID Number with the number of times the value of "0" appears consecutively. So, for the above table I would like to get the output as follows:

ID Number  Count of Consecutive 0 Values
754321     4 

Answer 1

This is a form of gaps-and-islands problem. You can assign each 0 a group by counting the number of non-zero values before it. Then aggregate.

However, SQL tables represent unordered sets. There is no ordering unless a column specifies the ordering. Let me assume you have one. Then:

select count(*)
from (select t.*,
             sum(values <> 0) over (partition by idnumber order by <ordering col>) as grp
      from t
     ) t
where values = 0
group by idnumber, grp;