Hi again,
if your looking for raising sub-totals, i found you thos form :
mysql> select 'TOTAL',
-> sum(if(DATE_FORMAT( `paymentDate` , '%Y-%m' )<='2005-01',amount,0)) as
'2005-01',
-> sum(if(DATE_FORMAT( `paymentDate` , '%Y-%m' )<='2005-02',amount,0)) as
'2005-02'
-> from payments
-> group by DATE_FORMAT( `paymentDate` , '%Y' );
+-------+---------+---------+
| TOTAL | 2005-01 | 2005-02 |
+-------+---------+---------+
| TOTAL | 200 | 258 |
+-------+---------+---------+
1 row in set (0.00 sec)
Hope that helps.
Mathias
******************************
Selon [EMAIL PROTECTED]:
> 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]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
