Count/Sum Question

15 pts.
Tags:
COUNTIF statement
MySQL functions
SQL commands
Hi I have a field with only 2 values. 0 and -1. I am interested in counting the -1's that occur only if they occur 4 or more times consecutively. Therefore, if the output were 0, 0, -1, -1, 0 the output would be 0. However, if it were 0,0,-1,-1,-1,-1,-1, 0, 0, -1,-1,-1,-1 the sum would be 9. Help please. Thanks

Software/Hardware used:
My SQLBrowser

Thanks. We'll let you know when a new response is added.

Your question is not very clear. You say “field” and refer to data being “consecutive”, but also used the tag “SQL”. Is this data in a table in an RDBMS?

If so, then the answer is that you cannot guarantee how the data will actually be accessed, hence there is no way to tell “consecutive” rows, unless the rows all have some sort of timestamp of when the row was added. Remember, there is no actual sequence to data in an RDBMS. Yes, you can use an ORDER BY on a result set, but that is applied just before the result set is given back the application that issued the SQL.

Now, you might be able to have physically consecutive data in a table if you were to SORT the data into your required sequence and then LOAD the data to your table. Just so long as no one adds any rows or otherwise messes around with the physical sequence, then the data would be physically in sequence. Next you would want to ensure that when accessing the data you did not use any indices, but rather read all the rows in the table. Then, you could have a physical manifestation of “consecutive records” but I suspect I am missing something in your question.

Steve

——————— kccrosser
Based on the new info, I would certainly do this as a SQL function with a cursor. It would be possible to create a very ugly query expression to compute this, but it is far easier to write a simple function, e.g.:

declare @total int
declare @temp int
declare @value int
declare cur cursor for select id, value from <thetable> order by id
set @total = 0
set @temp = 0
open cur
fetch next from cur into @id, @value
while @@fetch_status = 0
begin
if @value = -1
begin
set @temp = @temp + 1
if @temp >= 4
set @total = @total + 1
end
else
set @temp = 0
fetch next from cur into @id, @value
end
close cur
deallocate cur

Discuss This Question: 3 Replies

Thanks. We'll let you know when a new response is added.