Re: multi period sum() selects

[Michael Stassen]

It will, but BETWEEN is inclusive of both endpoints, so there would be a 
possibility of overlap with

  SELECT SUM(IF(timestamp BETWEEN P1 AND P2,1,0)) period1,
         SUM(IF(timestamp BETWEEN P2 AND P3,1,0)) period2,
         SUM(IF(timestamp BETWEEN P3 AND P4,1,0)) period3,
         ...
  FROM yourtable ...
because timestamp = P2 is in both period1 and period2, timestamp = P3 is in 
both period2 and period3, and ...

Michael
Sapenov wrote:
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]