Count rows after non zero

Question

Count rows after non zero

saxsena_sanjay

SSC Enthusiast

Points: 115
More actions
October 22, 2014 at 2:06 am

#291846
How to count rows after first non zero in a set? Example in the set data that have 2 column month and quantity, I want to start the count from the third row. The result should be 5. The query should ignore the top rows that has zero till a non zero number and count all rows after that even if there is zero.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

What is your expected output?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

saxsena_sanjay SSC Enthusiast Points: 115 More actions · Answer 2

The output of the sample data set in question should be 5.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

You can add a row number to each line with ROW_NUMBER(). Filter on non-zero quantities and get the minimum row number. Substract this from the maximum row number.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

saxsena_sanjay SSC Enthusiast Points: 115 More actions · Answer 4

Koen Verbeeck (10/22/2014)
You can add a row number to each line with ROW_NUMBER(). Filter on non-zero quantities and get the minimum row number. Substract this from the maximum row number.

hi Thanks. I have tested it and your idea works, just that it would be "Subtract the filtered minimum row number - 1 from the maximum. This forum rocks!

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

saxsena_sanjay (10/22/2014)
Koen Verbeeck (10/22/2014)
You can add a row number to each line with ROW_NUMBER(). Filter on non-zero quantities and get the minimum row number. Substract this from the maximum row number.
hi Thanks. I have tested it and your idea works, just that it would be "Subtract the filtered minimum row number - 1 from the maximum. This forum rocks!

Yeah, I was not sure if you had to subtract 1 from the minimum row number or not and didn't have time to test it all out.

Glad your problem is solved.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP