Count/Sum Question
Home > IT Answers > Microsoft Windows > Count/Sum Question
Chhmusic
15 pts.
0
Q:
Count/Sum Question
COUNTIF statement, SQL commands, MySQL functions
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
ASKED: Sep 22 2009  7:52 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Kccrosser
1850 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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
Last Answered: Sep 23 2009  3:42 PM GMT by Kccrosser   1850 pts.
Latest Contributors: Meandyou   1840 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29855 pts.  |   Sep 23 2009  1:55PM GMT

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…

 

Chhmusic   15 pts.  |   Sep 23 2009  3:27PM GMT

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

 

Carlosdl   29855 pts.  |   Sep 23 2009  6:14PM GMT

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

 
0