Number range in MySql

Question

Good day, I would like to count the zero value in column one.

Here's my code,

private void TextBox1_TextChanged(object sender, EventArgs e)
        {
            Db_Connection FormDBConnection = new Db_Connection();//db_connection
            FormDBConnection.ClassDBConnection_Infinity();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = Db_Connection.con;
            //DBConnection.con.Open();
            if (textBox1.Text == "01")
            {
                MySqlCommand cmd1 = new MySqlCommand("Select Count(*) From db_range WHERE one = '0' ", Db_Connection.con);
                var count1 = cmd1.ExecuteScalar();
                textBox2.Text = count1.ToString();
                if (Convert.ToInt32(textBox2.Text) == 0)
                {
                    textBox2.Text = "easy";
                }
                else if (Convert.ToInt32(textBox2.Text) <= 2)
                {
                    textBox2.Text = "Med";
                }
                else if (Convert.ToInt32(textBox2.Text) >= 3)
                {
                    textBox2.Text = "Long";
                }
            }
         
            
        }

My problem is, I want to count the latest input of 0(zero) not included the other old 0(zero) value.

Answer 1

Hi @YUI-012 , Welcome to Microsoft Q&A,

You seem to be using MySQL database. Your problem is mainly to find out how many latest consecutive 0.

        string query = @"
            SELECT COUNT(*) AS ZeroCount
            FROM (
                SELECT value1, 
                       @isZero := IF(value1 = 0 AND @isZero = 1, 1, 0) AS isZeroFlag
                FROM your_table, (SELECT @isZero := 1) AS init
                ORDER BY id DESC
            ) AS consecutiveZeros
            WHERE isZeroFlag = 1;
        ";

@isZero: This is a MySQL session variable. We initialize @isZero to 1 (assuming it is 0 at the beginning), then iterate over each row, if value1 is 0 and the previous one is also 0, isZero remains 1, otherwise it is set to 0. Sorting: Sort the records by id DESC, starting with the latest record. Filtering: Finally, we filter the rows with isZeroFlag = 1, indicating that they belong to the continuous 0 starting from the latest one.

Then you can judge the result.

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.