Re: Cumulative Totals
cumulative total index ... SELECT SUM(amount) , DATE_FORMAT( `paymentDate` , '%Y-%m' ) FROM `payments` GROUP BY DATE_FORMAT( payments . date , '%Y-%m' ) . Is there any way to get a running cumulative total directly from mysql? Something like: amount | paymentDate 200| 2005-01 258| 2005-02 Will WITH ROLLUP do what you want ? SELECT SUM(amount) , DATE_FORMAT( `paymentDate` , '%Y-%m' ) FROM `payments` GROUP BY DATE_FORMAT( payments . date , '%Y-%m' ) WITH ROLLUP This will give you something like: amount | paymentDate 200 | 2005-01 58 | 2005-02 258 | NULL Will it do ? -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cumulative Totals
From this discussion, I'm assuming that there is no support for a cumulative total index, is this correct? In other words, I'm looking for an index which lets me query a cumulative sum of a column in constant time, and which lets me find a row which has for instance the smallest cumulative sum above a certain value in constant time as well. I'm in the process of implementing B-trees in procedural SQL so that I can do this efficiently, but I wanted to make sure I wasn't duplicating anyone's effort. Frederik On Fri, May 27, 2005 at 03:07:24PM -0400, [EMAIL PROTECTED] wrote: Just in case you did not follow this suggestion, if you are using 4.0.x this is very simple. I was looking for this: set @total:=0; select f1,f2,...,@total:[EMAIL PROTECTED] as Total from table where ...; is pretty simple. On Wed, 25 May 2005, Dan Bolser wrote: On Wed, 25 May 2005, Russell Horn wrote: 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 http://dev.mysql.com/doc/mysql/en/variables.html :D Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- http://ofb.net/~frederik/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cumulative Totals
Just in case you did not follow this suggestion, if you are using 4.0.x this is very simple. I was looking for this: set @total:=0; select f1,f2,...,@total:[EMAIL PROTECTED] as Total from table where ...; is pretty simple. On Wed, 25 May 2005, Dan Bolser wrote: On Wed, 25 May 2005, Russell Horn wrote: 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 http://dev.mysql.com/doc/mysql/en/variables.html :D Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cumulative Totals
On Wed, 25 May 2005, Russell Horn wrote: 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 http://dev.mysql.com/doc/mysql/en/variables.html :D Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cumulative Totals
You may be able to use the WITH ROLLUP option of GROUP BY to get something of what you are looking for. Can't think of anything off the top of my head to get exactly what you are looking for. On May 25, 2005, at 8:02 AM, Russell Horn wrote: 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 Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cumulative Totals
- 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:
EMPNOSALARYRUNNING_SUM
1 25000.0025000.00
218000.0043000.00
332000.0075000.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.
*
* pGiven 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
*
* pFor example, given this table:
* xmp
* EMPNOSALARY
* 125000.00
* 218000.00
* 332000.00
* /xmp
* /p
*
* pand this query:/p
* xmpselect empno, salary, runningSum(salary) as running_sum from
mytable/xmp
*
* pThe query will return:/p
* xmp
* EMPNO SALARY RUNNING_SUM
* 1 25000.00 25000.00
* 2 18000.00 43000.00
* 3 32000.00 75000.00
* /xmp
*
* pIn 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
Re: Cumulative Totals
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:
EMPNOSALARYRUNNING_SUM
1 25000.0025000.00
218000.0043000.00
332000.0075000.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.
*
* pGiven 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
*
* pFor example, given this table:
* xmp
* EMPNOSALARY
* 125000.00
* 218000.00
* 332000.00
* /xmp
* /p
*
* pand this query:/p
* xmpselect empno, salary, runningSum(salary) as running_sum from
mytable/xmp
*
* pThe query will return:/p
* xmp
* EMPNO SALARY RUNNING_SUM
* 1 25000.00 25000.00
* 2 18000.00 43000.00
* 3 32000.00 75000.00
* /xmp
Re: Cumulative Totals
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:
EMPNOSALARYRUNNING_SUM
1 25000.0025000.00
218000.0043000.00
332000.0075000.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
