You don't give a lot of details, but perhaps something like
SELECT SUM(IF(timestamp>=P1 AND timestamp<P2,1,0)) period1,
SUM(IF(timestamp>=P2 AND timestamp<P3,1,0)) period2,
SUM(IF(timestamp>=P3 AND timestamp<P4,1,0)) period3,
...
FROM yourtable ...would do the trick. That counts rows. If you want to add up the values in the counter column for each period, you'd change it to
SELECT SUM(IF(timestamp>=P1 AND timestamp<P2,counter,0)) period1,
SUM(IF(timestamp>=P2 AND timestamp<P3,counter,0)) period2,
SUM(IF(timestamp>=P3 AND timestamp<P4,counter,0)) period3,
...
FROM yourtable ...You could use CREATE...SELECT or INSERT...SELECT to save the results in a separate stats table.
Michael
Bgs wrote:
Greetings,
While waiting for the repair table reactions I thought to ask a question that hogged in my head for a while.
I have a db which has among others (including text fields) a timestamp field and a counter field.
I want to make statistics from them, doing sum()s with conditions 'timestamp>P1 and timestamp<P2' 'timestamp>P2 and timestamp<P3' and so on.
A trivial way would be to make a cycle for Pn but that takes a lot of time and as one search takes considerable time (10-20sec for 100-200MB db), this multiplies to a huge overall time.
Any ideas how to solve this with possibly one db pass ?
Thanks Bgs
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
