I think all you need is a GROUP BY. Here is a tutorial sample from the manual: http://dev.mysql.com/doc/mysql/en/Counting_rows.html
Here is page that describes the full SELECT syntax, including GROUP BY http://dev.mysql.com/doc/mysql/en/SELECT.html And here are all of the other functions you can use with GROUP BY http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html Just guessing but your query should look something like: select state , avg(value) as mean , count(value) as population , std(value) as std_deviation , variance(value) as variance from timeseries_data_table group by state Uh- OH........ I just re-read your example and realized that I had the situation wrong. The second set of state=0 records would be grouped in with the first pair. Without some other means of differentiating one group of state values from another group __by the data__ and not __by their position__, SQL cannot accomplish what you want. The second pair of state=0 records is different from the first pair only because the records before them had a different state value (state=1). SQL is not meant to process information in this type of linear fashion. A cursor-based query _may_ be available to you IF you are running the bleeding edge MySQL server (5.x+). I don't run that version so I can't tell you what's working yet and what isn't. IMHO, I believe you will need to script a solution that scrolls through the records in sequence in order to detect the change in state and compute each "group"'s statistics on the fly. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/22/2004 07:48:45 AM: > I have the following table definition for time series data: > > ID (int) time (DATETIME) state (int) value (int) > > I want to make a state based grouping and calculate the mean of > each grouping. The state based grouping should be done by creating a new > group whenever the state changes, from one point in time to another. > > To explain what I mean I have made a small example: > > ID time state value > 1 2004-01-01 00:00 0 5 > 2 2004-01-01 00:02 0 3 > 3 2004-01-01 00:04 1 7 > 4 2004-01-01 00:07 1 9 > 5 2004-01-01 00:08 1 2 > 6 2004-01-01 00:10 0 2 > 7 2004-01-01 00:12 0 1 > 8 2004-01-01 00:13 2 2 > 9 2004-01-01 00:14 2 4 > 10 2004-01-01 00:15 2 2 > > The grouping for the above table would then be: (1,2); > (3,4,5); (6,7); (8,9,10), as the state changed at ID 3, 6, and 8. > > How do express a select statement in SQL which gives me the mean of each > group? > > Greetings, > > Mads Lindstrøm > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
