Bgs wrote: [...]
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 ?
The combination of SUM() and IF() could be used:
SELECT sum(if(timestamp>P1 and timestamp<P2,count,0)) "P1-P2", sum(if(timestamp>P2 and timestamp<P3,count,0)) "P2-P3", sum(if(timestamp>P3 and timestamp<P4,count,0)) "P3-P4",
...
FROM table;
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
