【Question】

I want to count all rows that are less than 10 in column "value" but if the next row(s) also have a value less than 10 only count it as 1(as a group). So in this example I would like to get back the number 3. I use MySQL:

So id-2 with value(3) < 10 = +1
Id-4,5,6 value < 10 = +1
Id-9 value < 10 = +1

-- id -- value --

|.. 1 ..|.. 11 ...|

|.. 2 ..|... 3 ...|

|.. 3 ..|.. 12 ...|

|.. 4 ..|... 2 ...|

|.. 5 ..|... 1 ...|

|.. 6 ..|... 3 ...|

|.. 7 ..|.. 15 ...|

|.. 8 ..|.. 24 ...|

|.. 9 ..|... 5 ...|

|. 10 ..|.. 15 ...|

【Answer】

My first reaction is comparing a value with the next one to group rows into multiple groups without aggregation. The groups are value≥10, value＜10 and value≥10 … Then count the groups where values are less than 10. SQL prescribes that grouping and aggregation are a bundle. And it’s an inconvenient workaround to compare a value with its next neighbor in the language. The following script written in SPL (Structured Process Language) is intuitive and easy to understand.

A1: Retrieve data in SQL;

A2: Perform merge grouping by comparing a value with the next one by value≥10 and value＜10;

A3: Count groups where value<10.