----- Original Message -----
From: "Russell Horn" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Wednesday, May 25, 2005 8:02 AM
Subject: Cumulative Totals
> I have a pretty simple table with a list of payments, not much more
> than:
>
> paymentID | amount | paymentDate
> 1 | 123 | 2005-01-10
> 2 | 77 | 2005-01-13
> 3 | 45 | 2005-02-16
> 4 | 13 | 2005-02-17
>
>
> I can get totals per month using a query like:
>
> SELECT SUM(amount) , DATE_FORMAT( `paymentDate` , '%Y-%m' ) FROM
> `payments` GROUP BY DATE_FORMAT( payments . date , '%Y-%m' )
>
> That would give me:
>
> amount | paymentDate
> 200 | 2005-01
> 58 | 2005-02
>
> Is there any way to get a running cumulative total directly from mysql?
> Something like:
>
> amount | paymentDate
> 200 | 2005-01
> 258 | 2005-02
>
I'm not sure if this solution will appeal to you but I satisfied the same
requirement in DB2 by writing a UDF (User-Defined Function). The function
was called RunningSum() and was invoked as follows:
select empno, salary, runningSum(salary) as running_sum from mytable
and gave the following result:
EMPNO SALARY RUNNING_SUM
00001 25000.00 25000.00
00002 18000.00 43000.00
00003 32000.00 75000.00
I think that's the sort of thing you want, right?
Now, I haven't actually tried porting my UDF over to MySQL so I'm not sure
if you could even make it work in MySQL. I believe you need to be using
Version 5.x of MySQL to be able to use UDFs and I'm still on Version 4.0.x.
The other thing you need for my UDF to work in MySQL would be a scratchpad
area. I've just skimmed the UDF section of the MySQL manual but can't see
anything that says clearly one way or the other whether they support
scratchpads or something functionally equivalent.
I wrote my function in Java, which is apparently not supported in MySQL but
the function itself is only a couple of lines long and doesn't use any
features unique to Java. You should be able to do the same work in C or C++
or (probably) SQL. The logic is very basic:
a. You set up a scratchpad or global variable that is initialized to zero. I
called this variable runningTotal. In my case, it is defined as a BigDecimal
but you should be able to use a comparable C/C++/SQL datatype, like double.
b. The function accepts a value from the column that is its argument. In the
example above, that is the salary column. The function adds that value to
the runningTotal variable.
c. The function is called once for each row of the table that satisfies the
query.
Each time it is called, the function returns runningTotal and displays it
(after the new value has been added to runningTotal).
In Java, this function looks like this (diagnostic code snipped). My
function was written to handle decimal numbers but you could change it very
easily to handle integers by changing 'BigDecimal' to 'int' each time it
occurs (and removing the import for java.math.BigDecimal).
============================================================================
===========================================================
package db2v8.udf.db2general;
import java.io.BufferedWriter;
import java.math.BigDecimal;
import COM.ibm.db2.app.UDF;
/**
* Class RunningSum contains various methods for DB2 UDFs that involve
keeping running
* totals of numeric columns.
*
* @version 1.0
* @since 2005-03-03
*/
public class RunningSum extends UDF {
/**
* The scratchpad for the runningSum() method. It *must* be initialized
outside of
* the method.
*/
private BigDecimal runningTotal = new BigDecimal(0.0);
/**
* Method runningSum() is used to accumulate a running total for a given
column of a
* table.
*
* <p>Given a database query that returns multiple rows and a numeric
input 'amount',
* this function returns that the sum of the current amount and the
total of the
* previous amounts in the same column.</p>
*
* <p>For example, given this table:
* <xmp>
* EMPNO SALARY
* 00001 25000.00
* 00002 18000.00
* 00003 32000.00
* </xmp>
* </p>
*
* <p>and this query:</p>
* <xmp>select empno, salary, runningSum(salary) as running_sum from
mytable</xmp>
*
* <p>The query will return:</p>
* <xmp>
* EMPNO SALARY RUNNING_SUM
* 00001 25000.00 25000.00
* 00002 18000.00 43000.00
* 00003 32000.00 75000.00
* </xmp>
*
* <p>In other words, the runningSum() function will return the actual
salary of the first
* employee on the first row of the result, the sum of the salaries of
the first two
* employees on the second row of the result, and the sum of the
salaries of the first
* three employees on the third row of the result.</p>
*
* @version 1.0
* @since 2005-03-03
*
* @return int the total of the values in the input column that have
been read so far
* @throws Exception
*/
public void runningSum(BigDecimal amount, BigDecimal sum) throws
Exception {
try {
/*
* Add the amount from the current row to the sum.
*/
runningTotal = runningTotal.add(amount);
/* Return the value of the running total. */
set(2, runningTotal);
} catch (Exception excp) {
excp.printStackTrace();
throw new IllegalArgumentException("runningSum() - " +
excp.getMessage());
}
}
}
============================================================================
===========================================================
Like I said, you may not want to get into something like this but I think it
is the best way to handle your requirement. It will make it very easy for
you to get running totals in the future and helps keep your SQL very simple.
Rhino
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.322 / Virus Database: 266.11.16 - Release Date: 24/05/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
