Use GROUP BY:
SELECT state, AVG(value) FROM yourtable GROUP BY state;
See the manual for all the details <http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html>.
Michael
[EMAIL PROTECTED] wrote:
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]