Hi,
aren't you looking for somthing like that :
mysql> select sum(amount),DATE_FORMAT( `paymentDate` , '%Y-%m' ) c FROM
`payments` GROUP BY c with rollup;
+-------------+---------+
| sum(amount) | c |
+-------------+---------+
| 200 | 2005-01 |
| 58 | 2005-02 |
| 258 | NULL |
+-------------+---------+
3 rows in set (0.00 sec)
but this is not really far from what you did :o)
Mathias
Selon Rhino <[EMAIL PROTECTED]>:
>
> ----- 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]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
