How do I use sql to select this

Question

I have the following data:

and I don't know how to use one sentence to query the continuous value(one = 1) when position is 2.the result should like this:

maxCount

   3

Answer 1

You should elaborate your logic in OP, not the comment above, this will help better, here you can try following sql and check if it's what you want or not:

SELECT
    MAX(@cnt := CASE WHEN one = 1 THEN  @cnt + 1 ELSE 0 END) AS maxCount
FROM yourtable
CROSS JOIN ( SELECT @cnt := 0 ) t
WHERE `position` = 2
ORDER BY id

Also you can check SQLFiddle Demo.

Answer 2

If you are summing them, use the SUM function.

SELECT SUM(one) FROM table WHERE position = 2;

Otherwise if just counting use the COUNT function.

SELECT COUNT(*) FROM table WHERE position = 2 AND one = 1

If you want a running count, do it in your program, you're asking the database to do something that really belongs in your loop that loads these records.