I wonder, if BETWEEN will work inside IF statement...
----- Original Message ----- From: "Roger Baklund" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "Bgs" <[EMAIL PROTECTED]>
Sent: Wednesday, December 01, 2004 10:28 AM
Subject: Re: multi period sum() selects
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
