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

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    I agree with Meandyou. You will need to provide more details, such as the complete table structure, the name of the column(s) you will use to order the results (and to be able to talk about "consecutive" records), confirm if you are using MySql and that you want to do this using SQL, etc...
    68,405 pointsBadges:
    report
  • Chhmusic
    Hi and thanks for responding to my question: Yes it is a MySQL database and I am using MySQL Browser to query with (or attempting to!). I will give more details: We are monitoring via a service for availability - this service is 'pinged' every minute. If it is available the Value = '0', if it is not available '-1'. However, our grade is dinged only if the service is not available for >= 4 consecutive minutes. The table has 3 columns: ID Value Date_Time Hopefully this will give you enough - it has been years and years since I last created an SQL query with if the smallest amount of complexity. chhmusic
    15 pointsBadges:
    report
  • carlosdl
    Kccrosser's solution seems to be in T-SQL syntax, so you will need to adjust it to the MySql syntax, and I think it will be possible only if you are using MySql 5.0 (or above). You might find these links useful: MySQL Stored Functions MySql 5.0 Cursors
    68,405 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following