i have a row with some simple events in it, in my case it could be "red" or "green", yeah i could also call it 1 and 0 but i think its a bit easier to explain this way. they are sorted by a timestamp, so i get something out like this in my table: green green green red green red red green green red green red ... what i want now is a list how much green and red phases i have and how long they are, and it dosnt even matters if this phases are red or green, so for this example i need something like this as result: phase length, count 3, 1 (3x green) 2, 2 (1x red, 1x green) 1, 5 (i dont really need this 1-phase results at the end...) anyone with a smart idea how to solve this in mysql?
what i want now is a list how much green and red phases i have and how long they are, and it dosnt even matters if this phases are red or green, so for this example i need something like this as result: phase length, count 3, 1 (3x green) 2, 2 (1x red, 1x green) 1, 5 (i dont really need this 1-phase results at the end...) please can you re-explain what you want in your result...it's not clear to me
It looks like you trying to track repeating trends (red or green), correct? You'll have to use a CURSOR or loop of some sort. This will allow you to increment the 1x count by one each time the current value equals the previous. Make sense? If not, I can try to put something together in SQL. Are you using mySql or Sql Server?
There is no single query that can do this for you. You can do this in php. Just loop thru your resultset and store your phase/count in an array.
its not possible to do this in atleast mysql4 alone. mysql5 its possible with stored procs. but if ur using mysql4 alone u must loop thro the result set in php