Re: Best method to keep totals
Just another quick question - following my initial question regarding the best method to keep totals, I wondered if I should adopt a table to record team league standings like based on each result like team_id home_win home_draw home_loss home_goals home_conceded away_win away_draw away_loss away_goals away_conceded Or should I try and display the league standings, based on the fixtures/results table which contains fields like match_id seasons_id week_number home_team_id away_team_id home_goals away_goals Cheers Neil On Sun, Sep 5, 2010 at 7:32 AM, Neil Tompkins wrote: > Thanks for all the useful information. I'm going to ensure the relevant > fields are indexed and our db is optimised. > > > > > On 4 Sep 2010, at 16:10, Arthur Fuller wrote: > > 100% agreed. >> >> Arthur >> >> The other exception is also where financial data is being stored. If you >> >>> have, say, a database containing sales order records, then as well as >>> storing the individual values of each item in each order, you also need >>> to >>> store the total value of the order, the total price charged to the >>> customer >>> and the total paid by the customer. These three should, of course, be not >>> only identical to each other but also to the sum of the individual items, >>> so >>> there is not only duplication but the potential for skew. But that, of >>> course, is precisely *why* you store them, as any discrepancy indicates >>> an >>> error which needs to be investigated. >>> >>> Mark >>> >>>
Re: Best method to keep totals
Thanks for all the useful information. I'm going to ensure the relevant fields are indexed and our db is optimised. On 4 Sep 2010, at 16:10, Arthur Fuller wrote: 100% agreed. Arthur The other exception is also where financial data is being stored. If you have, say, a database containing sales order records, then as well as storing the individual values of each item in each order, you also need to store the total value of the order, the total price charged to the customer and the total paid by the customer. These three should, of course, be not only identical to each other but also to the sum of the individual items, so there is not only duplication but the potential for skew. But that, of course, is precisely *why* you store them, as any discrepancy indicates an error which needs to be investigated. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best method to keep totals
100% agreed. Arthur The other exception is also where financial data is being stored. If you > have, say, a database containing sales order records, then as well as > storing the individual values of each item in each order, you also need to > store the total value of the order, the total price charged to the customer > and the total paid by the customer. These three should, of course, be not > only identical to each other but also to the sum of the individual items, so > there is not only duplication but the potential for skew. But that, of > course, is precisely *why* you store them, as any discrepancy indicates an > error which needs to be investigated. > > Mark >
Re: Best method to keep totals
On 03/09/2010 16:32, Arthur Fuller wrote:
While I agree with the general take on this subject ("Never store totals
without a good reason" and "where there is duplication there is the
opportunity for skew"), I must say that there are exceptions. A couple of
years ago I worked on an inherited database in which the operant principle
was "sum don't store"; the problem was that many of the rows summed dated
back a year or two or more, and as an accountant friend of mine loved to
say, "A paid transaction is history; an unpaid transaction is fiction."
The other exception is also where financial data is being stored. If you
have, say, a database containing sales order records, then as well as
storing the individual values of each item in each order, you also need
to store the total value of the order, the total price charged to the
customer and the total paid by the customer. These three should, of
course, be not only identical to each other but also to the sum of the
individual items, so there is not only duplication but the potential for
skew. But that, of course, is precisely *why* you store them, as any
discrepancy indicates an error which needs to be investigated.
Mark
--
http://mark.goodge.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best method to keep totals
While I agree with the general take on this subject ("Never store totals
without a good reason" and "where there is duplication there is the
opportunity for skew"), I must say that there are exceptions. A couple of
years ago I worked on an inherited database in which the operant principle
was "sum don't store"; the problem was that many of the rows summed dated
back a year or two or more, and as an accountant friend of mine loved to
say, "A paid transaction is history; an unpaid transaction is fiction." In
other words, repeatedly summing amounts dating from last year or the year(s)
before is a waste of time and energy. An approach much superior in
performance is to store History in one summary table and Current in the
actual transaction table. Then all no sums or other calculations are
required for the History portion of the final calculation or presentation or
whatever it is. You grab and sum this (fiscal) year's rows, and then look up
the numbers for previous year(s). When you're dealing with say a million
rows per year, this change can dramatically improve performance.
Just my $0.02".
Arthur
Re: Best method to keep totals
On 01/09/2010 10:29 p, Tompkins Neil wrote: Hi, I'm developing a system whereby a manager gets assigned points based a certain number of factors which are saved alongside a football result. However, I also want to keep a total for each manager. My question is it best to just have a query that uses SUM to total the managers points gained for each fixture participated in ? Or should I be looking to have a separate field which is the calculated total for each manager ? Thanks in advance for any advice. Cheers Neil Neil, its generally not a good idea to store totals. I also had that problem when i thought that as data grows it will slow down the server to a halt. We have a mobile financial system with a table called trns that stores all the credits and debits per account. each time a customer wants his balance we sum up all the debits and credits (debits being negative) and reply with the answer. We have close to 1 million million people and each person does around 2 transactions per month, so the trn table grows 2 million records every month we are in our 3rd month. and select sum(t_amount) from trn where t_acno = works like a charm! of course t_acno is indexed and the server is optimized. In addition we sum before a debit to see if the balance is suffecient, before a transfer, blah blah blah ... So it's all good. -- Jangita | +256 76 91 8383 | Y! & MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best method to keep totals
On 2 Sep 10, at 13:58, Jerry Schwartz wrote: >> From: Jan Steinman [mailto:j...@bytesmiths.com] >> >>> From: "Jerry Schwartz" >>> >>> IMNSHO, never store dynamic data in a field unless you absolutely have to. ... >> To do something similar in MySQL, I've used views (which is somewhat clumsy), >> but is there a better way? >> > [JS] Wouldn't the appropriate place for the calculation be... > in a store procedure that they share? That's basically > the same as a calculated field. I've never played with stored procedures. Can you point me to a tutorial? Or should I just Google for it? Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Best method to keep totals
>-Original Message- >From: Jan Steinman [mailto:j...@bytesmiths.com] >Sent: Thursday, September 02, 2010 1:52 PM >To: mysql@lists.mysql.com >Subject: RE: Best method to keep totals > >> From: "Jerry Schwartz" >> >> IMNSHO, never store dynamic data in a field unless you absolutely have to. > >I agree, and yet, it's so darned handy if it's a calculation you need quite >often. > [JS] You run the risk of introducing inconsistencies. >In FileMaker Pro (hold the "boos," please :-) you can have calculated >fields -- >a "pseudo field" that holds references to other fields in an equation. > [JS] I've used similar products. I suspect that the calculations are done on demand just as they would be with MySQL. >To do something similar in MySQL, I've used views (which is somewhat clumsy), >but is there a better way? Or is this necessarily part of domain knowledge >programming in your interface language? > [JS] Wouldn't the appropriate place for the calculation be in whatever queries retrieve the data, or in a store procedure that they share? That's basically the same as a calculated field. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com > > > Jan Steinman, EcoReality Co-op > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Best method to keep totals
> From: "Jerry Schwartz" > > IMNSHO, never store dynamic data in a field unless you absolutely have to. I agree, and yet, it's so darned handy if it's a calculation you need quite often. In FileMaker Pro (hold the "boos," please :-) you can have calculated fields -- a "pseudo field" that holds references to other fields in an equation. To do something similar in MySQL, I've used views (which is somewhat clumsy), but is there a better way? Or is this necessarily part of domain knowledge programming in your interface language? Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best method to keep totals
Thanks for your quick response. So like I thought, I'll just calculate the totals on the fly and like you mentioned the manager is going to participate in 50 games per season, with a season being twice per year. Thanks for the help. Neil On Wed, Sep 1, 2010 at 10:06 PM, Jerry Schwartz wrote: > IMNSHO, never store dynamic data in a field unless you absolutely have to. > > There are going to be exceptions, but unless a manager is going to > participate > in hundreds of thousands of fixtures I don't think this is one of them. > > Regards, > > Jerry Schwartz > Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > E-mail: je...@gii.co.jp > Web site: www.the-infoshop.com > > >-Original Message- > >From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] > >Sent: Wednesday, September 01, 2010 4:29 PM > >To: [MySQL] > >Subject: Best method to keep totals > > > >Hi, > > > >I'm developing a system whereby a manager gets assigned points based a > >certain number of factors which are saved alongside a football result. > > However, I also want to keep a total for each manager. My question is it > >best to just have a query that uses SUM to total the managers points > gained > >for each fixture participated in ? Or should I be looking to have > >a separate field which is the calculated total for each manager ? > > > >Thanks in advance for any advice. > > > >Cheers > >Neil > > > >
RE: Best method to keep totals
IMNSHO, never store dynamic data in a field unless you absolutely have to. There are going to be exceptions, but unless a manager is going to participate in hundreds of thousands of fixtures I don't think this is one of them. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com >-Original Message- >From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] >Sent: Wednesday, September 01, 2010 4:29 PM >To: [MySQL] >Subject: Best method to keep totals > >Hi, > >I'm developing a system whereby a manager gets assigned points based a >certain number of factors which are saved alongside a football result. > However, I also want to keep a total for each manager. My question is it >best to just have a query that uses SUM to total the managers points gained >for each fixture participated in ? Or should I be looking to have >a separate field which is the calculated total for each manager ? > >Thanks in advance for any advice. > >Cheers >Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Best method to keep totals
Hi, I'm developing a system whereby a manager gets assigned points based a certain number of factors which are saved alongside a football result. However, I also want to keep a total for each manager. My question is it best to just have a query that uses SUM to total the managers points gained for each fixture participated in ? Or should I be looking to have a separate field which is the calculated total for each manager ? Thanks in advance for any advice. Cheers Neil
Re: How to Get Running Totals.
>I am trying to get running totals See "Running sum" at http://www.artfulsoftware.com/infotree/queries.php. PB - Venugopal Rao wrote: I am trying to get running totals for the following: Project Code---R&D---STP--RunningTotal 1007303--04---04---8 1007304--04---04---16 I tried to follow the examples available on the site but it is giving errors. I am using java as a middleware and mysql as back end. I am trying ot use backend query for getting the results. response with examples will be more helpful. With regards, VR Venugopal Rao --- On Wed, 28/4/10, Jay Blanchard wrote: From: Jay Blanchard Subject: RE: Pivot Query in To: "VR Venugopal Rao" , "MySQL" Date: Wednesday, 28 April, 2010, 4:57 PM [snip] Date, ProjectCode Building, Number of Copies I want to get a Connsolidate Report of Project Code R&D STP 1007304--04---04 (Group by Project Code)(Sumtotal Building wise). I have tried to execute the following code : select pcode, building, sum(ncopies) from request group by pcode,building It is giving the following repott 1007304--R&D--04 1007304-STP--05 Here there is a repetition of pcode, How can I avoid this. I want to get the Building Name on top as Column Names and bottom I should get the Count. [/snip] We would need to see some of the raw data to help you but this older article may point you in the right direction with crosstab (pivot) queries; http://www.evolt.org/node/26896 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vr_v...@yahoo.co.in No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.437 / Virus Database: 271.1.1/2849 - Release Date: 05/02/10 06:27:00
How to Get Running Totals.
I am trying to get running totals for the following: Project Code---R&D---STP--RunningTotal 1007303--04---04---8 1007304--04---04---16 I tried to follow the examples available on the site but it is giving errors. I am using java as a middleware and mysql as back end. I am trying ot use backend query for getting the results. response with examples will be more helpful. With regards, VR Venugopal Rao --- On Wed, 28/4/10, Jay Blanchard wrote: From: Jay Blanchard Subject: RE: Pivot Query in To: "VR Venugopal Rao" , "MySQL" Date: Wednesday, 28 April, 2010, 4:57 PM [snip] Date, ProjectCode Building, Number of Copies I want to get a Connsolidate Report of Project Code R&D STP 1007304--04---04 (Group by Project Code)(Sumtotal Building wise). I have tried to execute the following code : select pcode, building, sum(ncopies) from request group by pcode,building It is giving the following repott 1007304--R&D--04 1007304-STP--05 Here there is a repetition of pcode, How can I avoid this. I want to get the Building Name on top as Column Names and bottom I should get the Count. [/snip] We would need to see some of the raw data to help you but this older article may point you in the right direction with crosstab (pivot) queries; http://www.evolt.org/node/26896 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vr_v...@yahoo.co.in
Re: Query for rolling totals
Thank you Brent, much appreciated! On Thu, 2009-09-03 at 14:12 -0400, Brent Baisley wrote: > You can do this using a variable. Set the variable starting value with > a "query": > set @runningTotal := 0 > > Then add the calculation to your total: > SELECT a. trans_id ,a.tran_date,b.cat_type,a.payee,a.amnt, > @runningTotal := @runningTotal+a.amnt AS rollingTotal from > transactions a join categories b on a.cat_id = b.cat_id where > a.user_id=1 and a.acc_id=3 order by a.tran_date ASC > > Brent Baisley > > On Thu, Sep 3, 2009 at 1:56 PM, John > Daisley wrote: > > Hi, > > > > Hoping someone can help me with this little issue! It seems really > > simple but my brain is refusing to work. > > > > We have a transactions tables like so... > > > > mysql> desc transactions; > > +---+--+--+-+-++ > > | Field | Type | Null | Key | Default | Extra > > | > > +---+--+--+-+-++ > > | trans_id | int(10) unsigned | NO | PRI | NULL| > > auto_increment | > > | user_id | smallint(5) unsigned | NO | MUL | NULL| > > | > > | acc_id| smallint(5) unsigned | NO | MUL | NULL| > > | > > | tran_date | date | NO | | NULL| > > | > > | payee | varchar(25) | NO | | NULL| > > | > > | amnt | decimal(8,2) | NO | | NULL| > > | > > | cat_id| int(10) unsigned | NO | MUL | NULL| > > | > > +---+--+--+-+-++ > > 7 rows in set (0.00 sec) > > > > > > > > ...this joins to a few other tables and has the following simple query > > run on it to show all transactions for a particular user and account. > > > > mysql> SELECT a. trans_id ,a.tran_date,b.cat_type,a.payee,a.amnt from > > transactions a join categories b on a.cat_id = b.cat_id where > > a.user_id=1 and a.acc_id=3 order by a.tran_date ASC; > > > > Which returns a list something like this... > > > > +---+-+--+---+--+ > > trans_id | tran_date| cat_type | payee | amnt | > > +---+-+-++--+ > > | 1| 2009-08-31 | Income | Opening Balance| 0.00 | > > | 3| 2009-09-02 | Income | Test Transactions | 0.20 | > > |23| 2009-09-02 | Income | Tester| 1.20 > > | > > | 102| 2009-09-02 | Income | Debit | -1.09 > > | > > +---+-+-++--+ > > 4 rows in set (0.00 sec) > > > > Now this has been fine for a long time until this afternoon when I get a > > call saying the query needs editing to add another column to the output > > showing a rolling account balance. This means the current output show > > above needs to change to something like this... > > > > +-+---++--+---+-+ > > |trans_id | |tran_date | cat_type | payee| amnt | > > Rolling Balance | > > +-+---++--+---+-+ > > | 1| 2009-08-31 | Income | Opening Balance | 0.00 | > > 0.00 | > > | 3| 2009-09-02 | Income | Test Transactions | 0.20 | > > 0.20 | > > | 23| 2009-09-02 | Income | Tester | 1.20 | > > 1.40 | > > | 102| 2009-09-02 | Income | Debit| -1.09 | > > 0.31 | > > +-+---++--+---+-+ > > 4 rows in set (0.00 sec) > > > > Anyone got any tips on how to achieve this? Group with ROLLUP doesn't > > really do what I need. I've tried a couple of sub queries but cant get > > the output I need. > > > > I want to avoid storing a rolling balance into the table because this > > would take a while due to the number of records and could create > > problems when someone goes in and modifies a single transaction. > > > > Any tips would be much appreciated. > > > > Regards > >
Re: Query for rolling totals
You can do this using a variable. Set the variable starting value with a "query": set @runningTotal := 0 Then add the calculation to your total: SELECT a. trans_id ,a.tran_date,b.cat_type,a.payee,a.amnt, @runningTotal := @runningTotal+a.amnt AS rollingTotal from transactions a join categories b on a.cat_id = b.cat_id where a.user_id=1 and a.acc_id=3 order by a.tran_date ASC Brent Baisley On Thu, Sep 3, 2009 at 1:56 PM, John Daisley wrote: > Hi, > > Hoping someone can help me with this little issue! It seems really > simple but my brain is refusing to work. > > We have a transactions tables like so... > > mysql> desc transactions; > +---+--+--+-+-++ > | Field | Type | Null | Key | Default | Extra > | > +---+--+--+-+-++ > | trans_id | int(10) unsigned | NO | PRI | NULL | > auto_increment | > | user_id | smallint(5) unsigned | NO | MUL | NULL | > | > | acc_id | smallint(5) unsigned | NO | MUL | NULL | > | > | tran_date | date | NO | | NULL | > | > | payee | varchar(25) | NO | | NULL | > | > | amnt | decimal(8,2) | NO | | NULL | > | > | cat_id | int(10) unsigned | NO | MUL | NULL | > | > +---+--+--+-+-++ > 7 rows in set (0.00 sec) > > > > ...this joins to a few other tables and has the following simple query > run on it to show all transactions for a particular user and account. > > mysql> SELECT a. trans_id ,a.tran_date,b.cat_type,a.payee,a.amnt from > transactions a join categories b on a.cat_id = b.cat_id where > a.user_id=1 and a.acc_id=3 order by a.tran_date ASC; > > Which returns a list something like this... > > +---+-+--+---+--+ > trans_id | tran_date | cat_type | payee | amnt | > +---+-+-++--+ > | 1| 2009-08-31 | Income | Opening Balance | 0.00 | > | 3| 2009-09-02 | Income | Test Transactions | 0.20 | > | 23| 2009-09-02 | Income | Tester | 1.20 > | > | 102| 2009-09-02 | Income | Debit | -1.09 > | > +---+-+-++--+ > 4 rows in set (0.00 sec) > > Now this has been fine for a long time until this afternoon when I get a > call saying the query needs editing to add another column to the output > showing a rolling account balance. This means the current output show > above needs to change to something like this... > > +-+---++--+---+-+ > |trans_id | |tran_date | cat_type | payee | amnt | > Rolling Balance | > +-+---++--+---+-+ > | 1| 2009-08-31 | Income | Opening Balance | 0.00 | > 0.00 | > | 3| 2009-09-02 | Income | Test Transactions | 0.20 | > 0.20 | > | 23| 2009-09-02 | Income | Tester | 1.20 | > 1.40 | > | 102| 2009-09-02 | Income | Debit | -1.09 | > 0.31 | > +-+---++--+---+-+ > 4 rows in set (0.00 sec) > > Anyone got any tips on how to achieve this? Group with ROLLUP doesn't > really do what I need. I've tried a couple of sub queries but cant get > the output I need. > > I want to avoid storing a rolling balance into the table because this > would take a while due to the number of records and could create > problems when someone goes in and modifies a single transaction. > > Any tips would be much appreciated. > > Regards > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query for rolling totals
Hi, Hoping someone can help me with this little issue! It seems really simple but my brain is refusing to work. We have a transactions tables like so... mysql> desc transactions; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | trans_id | int(10) unsigned | NO | PRI | NULL| auto_increment | | user_id | smallint(5) unsigned | NO | MUL | NULL| | | acc_id| smallint(5) unsigned | NO | MUL | NULL| | | tran_date | date | NO | | NULL| | | payee | varchar(25) | NO | | NULL| | | amnt | decimal(8,2) | NO | | NULL| | | cat_id| int(10) unsigned | NO | MUL | NULL| | +---+--+--+-+-++ 7 rows in set (0.00 sec) ...this joins to a few other tables and has the following simple query run on it to show all transactions for a particular user and account. mysql> SELECT a. trans_id ,a.tran_date,b.cat_type,a.payee,a.amnt from transactions a join categories b on a.cat_id = b.cat_id where a.user_id=1 and a.acc_id=3 order by a.tran_date ASC; Which returns a list something like this... +---+-+--+---+--+ trans_id | tran_date| cat_type | payee | amnt | +---+-+-++--+ | 1| 2009-08-31 | Income | Opening Balance| 0.00 | | 3| 2009-09-02 | Income | Test Transactions | 0.20 | |23| 2009-09-02 | Income | Tester| 1.20 | | 102| 2009-09-02 | Income | Debit | -1.09 | +---+-+-++--+ 4 rows in set (0.00 sec) Now this has been fine for a long time until this afternoon when I get a call saying the query needs editing to add another column to the output showing a rolling account balance. This means the current output show above needs to change to something like this... +-+---++--+---+-+ |trans_id | |tran_date | cat_type | payee| amnt | Rolling Balance | +-+---++--+---+-+ | 1| 2009-08-31 | Income | Opening Balance | 0.00 | 0.00 | | 3| 2009-09-02 | Income | Test Transactions | 0.20 | 0.20 | | 23| 2009-09-02 | Income | Tester | 1.20 | 1.40 | | 102| 2009-09-02 | Income | Debit| -1.09 | 0.31 | +-+---++--+---+-+ 4 rows in set (0.00 sec) Anyone got any tips on how to achieve this? Group with ROLLUP doesn't really do what I need. I've tried a couple of sub queries but cant get the output I need. I want to avoid storing a rolling balance into the table because this would take a while due to the number of records and could create problems when someone goes in and modifies a single transaction. Any tips would be much appreciated. Regards
Re: How to summarize weekly totals?
>Some days may be missing if it is a holiday, which means I can't >guarantee each week will have a sales row for Monday or Saturday. >Do I need to write a program to do this? Or can it be done with SQL? TIA Group by WEEK(sale_date), or build a calendar table and join from it. PB mos wrote: I can't get my head around this, but I think I should be able to do it using just SQL without writing a program. I have a simple table that looks like: Rcd_Id: Integer; Sale_Date: Date; Sale_Amt: Double; Each date will have 1 row with the total sales for that day. and I want to sum the weekly totals so it looks like: WeekEnding TotalAmt 2006-10-14 3899.34 2006-10-21 222.12 2006-10-28 33122.12 So for each week, it will sum the sales for Monday through Saturday and provides a total column. It will do this for all the rows in the table. If there was a years worth of data, there would be 52 weeks hence 52 rows of data would be produced. Some days may be missing if it is a holiday, which means I can't guarantee each week will have a sales row for Monday or Saturday. Do I need to write a program to do this? Or can it be done with SQL? TIA Mike No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.16/504 - Release Date: 10/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to summarize weekly totals?
I can't get my head around this, but I think I should be able to do it using just SQL without writing a program. I have a simple table that looks like: Rcd_Id: Integer; Sale_Date: Date; Sale_Amt: Double; Each date will have 1 row with the total sales for that day. and I want to sum the weekly totals so it looks like: WeekEndingTotalAmt 2006-10-143899.34 2006-10-21 222.12 2006-10-28 33122.12 So for each week, it will sum the sales for Monday through Saturday and provides a total column. It will do this for all the rows in the table. If there was a years worth of data, there would be 52 weeks hence 52 rows of data would be produced. Some days may be missing if it is a holiday, which means I can't guarantee each week will have a sales row for Monday or Saturday. Do I need to write a program to do this? Or can it be done with SQL? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Totals?
Well, I said earlier that if I found a solution to this, I'd post it. Here it is, with many thanks to Nicholas Bernstein's timely July 7 post to the doc on user variables: It's not particularly elegant, it just gets the job done. If there is a cleaner way to do this, I'm not ashamed to be educated. Barry * Compquery.sql -- Compare Current Year Reg Numbers and Money to Prior Year */ /* */ /* */ /* ACCUMULATE DATA BY MONTH FOR BOTH YEARS*/ Drop Table If Exists Montable, Montable2; Create Temporary Table Montable engine=memory Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex, Sum(Amount) as Paid From capclave2005reg where ( amount > 0) Group by Monindex; Create Temporary Table Montable2 engine=memory Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex, Sum(Amount) as Paid From Capclavepresent where ( amount > 0) Group by Monindex; /* REPORT FOR BOTH YEARS WITH RUNNING TOTALS*/ Set @cumreg=0, @cumreg2=0, @cumpd=0, @cumpd2=0; Select Month, Year, Registrations, Paid RegIncom, Monindex, @cumreg:[EMAIL PROTECTED] + Registrations RegYearToDate, @cumpd:= @cumpd+Paid RegIncomeYTD From Montable Union Select Month, Year, Registrations, Paid RegIncome, Monindex, @cumreg2:[EMAIL PROTECTED] + Registrations RegYearToDate, @cumpd2:= @cumpd2+Paid RegIncomeYTD From Montable2 ; Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Totals?
At 04:15 PM 8/3/2006, Brent Baisley wrote: You might look into WITH ROLLUP. That could easily give you cumulative totals for the year, but off the top of my head I can't think of a way to get it for the months. - Original Message - From: "Barry Newton" <[EMAIL PROTECTED]> To: Sent: Wednesday, August 02, 2006 10:29 PM Subject: Running Totals? Back with another registration db question: Have a convention database which tracks people as they register all year long; the actual convention is held in October. I've got a fairly simple query which shows how many people registered in each calendar month--useful to compare to prior year to see if we're at least on track with our count. It would make life easier if I could also show a column with the cumulative count for each month. The existing output is: That's what happens with ROLLUP. I'm looking into a possible subquery approach just now. If it works, it will be worth it's own post. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Totals?
You might look into WITH ROLLUP. That could easily give you cumulative totals for the year, but off the top of my head I can't think of a way to get it for the months. - Original Message - From: "Barry Newton" <[EMAIL PROTECTED]> To: Sent: Wednesday, August 02, 2006 10:29 PM Subject: Running Totals? Back with another registration db question: Have a convention database which tracks people as they register all year long; the actual convention is held in October. I've got a fairly simple query which shows how many people registered in each calendar month--useful to compare to prior year to see if we're at least on track with our count. It would make life easier if I could also show a column with the cumulative count for each month. The existing output is: +---+--+---+--+ | Month | Year | Registrations | Monindex | +---+--+---+--+ | October | 2004 |23 | 200410 | | December | 2004 | 5 | 200412 | | January | 2005 | 9 | 200501 | | February | 2005 |11 | 200502 | | April | 2005 | 2 | 200504 | | May | 2005 |48 | 200505 | | June | 2005 |45 | 200506 | | July | 2005 |10 | 200507 | | August| 2005 |17 | 200508 | | September | 2005 |58 | 200509 | | October | 2005 |97 | 200510 | +---+--+---+--+ The cumulative column would ideally show 23,28,37, etc. Also, if anyone has a better way to keep the different years apart than the 'monindex' column, or at least to suppress displaying it, I'll be really interested. The existing query is: Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex From capclave2005reg Where year(DatePaid)=2004 and (amount > 0 or Dealer = 'Y') Group by Monindex Union Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex From capclave2005reg where year(DatePaid)=2005 and (amount > 0 or Dealer = 'Y') Group by Monindex; Barry -- 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]
Re: Running Totals?
At 11:10 PM 8/2/2006, Peter Brawley wrote: Barry >It would make life easier if I could also show a column >with the cumulative count for each month. Set @cum - 0; Select Monthname(DatePaid) Month, Year(DatePaid) Year, Count(*) as Registrations, Extract(Year_Month from DatePaid) AS Monindex, @cum := @cum + Count(*) AS 'Year to date' >From capclave2005reg Where year(DatePaid)=2004 and (amount > 0 or Dealer = 'Y') Group by Monindex ; PB Looked promising, but gets me the following, which isn't quite right: +---+--+---+--+--+ | Month | Year | Registrations | Monindex | Year to date | +---+--+---+--+--+ | October | 2004 |23 | 200410 | 23 | | December | 2004 | 5 | 200412 |5 | | January | 2005 | 9 | 200501 | 14 | | February | 2005 |11 | 200502 | 16 | | April | 2005 | 2 | 200504 |7 | | May | 2005 |48 | 200505 | 53 | | June | 2005 |45 | 200506 | 50 | | July | 2005 |10 | 200507 | 15 | | August| 2005 |17 | 200508 | 22 | | September | 2005 |58 | 200509 | 63 | | October | 2005 |97 | 200510 | 102 | +---+--+---+--+--+ Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Totals?
Barry >It would make life easier if I could also show a column >with the cumulative count for each month. Set @cum - 0; Select Monthname(DatePaid) Month, Year(DatePaid) Year, Count(*) as Registrations, Extract(Year_Month from DatePaid) AS Monindex, @cum := @cum + Count(*) AS 'Year to date' >From capclave2005reg Where year(DatePaid)=2004 and (amount > 0 or Dealer = 'Y') Group by Monindex ; PB - Barry Newton wrote: Back with another registration db question: Have a convention database which tracks people as they register all year long; the actual convention is held in October. I've got a fairly simple query which shows how many people registered in each calendar month--useful to compare to prior year to see if we're at least on track with our count. It would make life easier if I could also show a column with the cumulative count for each month. The existing output is: +---+--+---+--+ | Month | Year | Registrations | Monindex | +---+--+---+--+ | October | 2004 | 23 | 200410 | | December | 2004 | 5 | 200412 | | January | 2005 | 9 | 200501 | | February | 2005 | 11 | 200502 | | April | 2005 | 2 | 200504 | | May | 2005 | 48 | 200505 | | June | 2005 | 45 | 200506 | | July | 2005 | 10 | 200507 | | August | 2005 | 17 | 200508 | | September | 2005 | 58 | 200509 | | October | 2005 | 97 | 200510 | +---+--+---+--+ The cumulative column would ideally show 23,28,37, etc. Also, if anyone has a better way to keep the different years apart than the 'monindex' column, or at least to suppress displaying it, I'll be really interested. The existing query is: Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex >From capclave2005reg Where year(DatePaid)=2004 and (amount > 0 or Dealer = 'Y') Group by Monindex Union Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex >From capclave2005reg where year(DatePaid)=2005 and (amount > 0 or Dealer = 'Y') Group by Monindex; Barry No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.5/404 - Release Date: 7/31/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running Totals?
Back with another registration db question: Have a convention database which tracks people as they register all year long; the actual convention is held in October. I've got a fairly simple query which shows how many people registered in each calendar month--useful to compare to prior year to see if we're at least on track with our count. It would make life easier if I could also show a column with the cumulative count for each month. The existing output is: +---+--+---+--+ | Month | Year | Registrations | Monindex | +---+--+---+--+ | October | 2004 |23 | 200410 | | December | 2004 | 5 | 200412 | | January | 2005 | 9 | 200501 | | February | 2005 |11 | 200502 | | April | 2005 | 2 | 200504 | | May | 2005 |48 | 200505 | | June | 2005 |45 | 200506 | | July | 2005 |10 | 200507 | | August| 2005 |17 | 200508 | | September | 2005 |58 | 200509 | | October | 2005 |97 | 200510 | +---+--+---+--+ The cumulative column would ideally show 23,28,37, etc. Also, if anyone has a better way to keep the different years apart than the 'monindex' column, or at least to suppress displaying it, I'll be really interested. The existing query is: Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex From capclave2005reg Where year(DatePaid)=2004 and (amount > 0 or Dealer = 'Y') Group by Monindex Union Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex From capclave2005reg where year(DatePaid)=2005 and (amount > 0 or Dealer = 'Y') Group by Monindex; Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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: colum totals
On Fri, 2005-09-16 at 14:02 +0100, Mark Leith wrote: > http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html > perfect, thank you. tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: colum totals
You can achieve this with the WITH ROLLUP option of GROUP BY. Although you would probably have to perform a trick on the "date" column to have the NULL value that WITH ROLLUP would return to return as "total" - i.e IFNULL(date,'Total') as a quick (and dirty) example, as this doesn't work too well on statements that have multiple GROUP BY columns ;) You can read more about WITH ROLLUP here: http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html Mark Leith > -Original Message- > From: Tony Leake [mailto:[EMAIL PROTECTED] > Sent: 16 September 2005 13:19 > To: mysql@lists.mysql.com > Subject: colum totals > > Hi, > > I'm sure I read somewhere about a new function that would > give the totals of all columns, ie if i have 2 cols, > numberOfOrders valueOfOrders for a range of dates i can get > something like the following > > > Date numberOfOrdersvalueOfOrders > 01-09 4 20.00 > 02-09 6 100.00 > total 10120.00 > > > could someone point me to this in the manual please as I > can't seem to find it. Or did i just imagine it anyway? > > thanks > > tony > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.344 / Virus Database: 267.11.0/103 - Release > Date: 15/09/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 15/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
colum totals
Hi, I'm sure I read somewhere about a new function that would give the totals of all columns, ie if i have 2 cols, numberOfOrders valueOfOrders for a range of dates i can get something like the following Date numberOfOrdersvalueOfOrders 01-09 4 20.00 02-09 6 100.00 total 10120.00 could someone point me to this in the manual please as I can't seem to find it. Or did i just imagine it anyway? thanks tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Totals Across Multiple Records
Sol and Peter, Thanks for your feedback. Both of your suggestions got me going in the right direction and I was able to solve the problem using temporary tables and left joins. Thanks again. Albert Padley On Jul 23, 2005, at 11:00 AM, sol beach wrote: create table count_temp1 select id, count(id) count_id from table ss; create table count_temp 2 select id, count(tt) from ss where id = sd1 or id = sd2; should get you closer. yes? On 7/22/05, Albert Padley <[EMAIL PROTECTED]> wrote: I would be grateful if those of you around this weekend could help me figure out if what I'm after is possible. I've already spent hours with the manual, the archives and my books. I've looked at JOINS and TEMP TABLES but still can't come up with a solution. THE ENVIRONMENT: MySQL Version 4.0.24 (so subselects are not available) THE TABLE CREATE TABLE `ss` ( `tt` INT NOT NULL AUTO_INCREMENT , `zz` INT( 3 ) NOT NULL , `sd1` INT( 3 ) NOT NULL , `sd2` INT( 3 ) NOT NULL , PRIMARY KEY ( `id` ) ); THE ISSUE: 1. xx, sd1 and sd2 all contain id numbers. These numbers are unique within each record. 2. I need to scan the table and create a table row for each id number in zz that contains: a. how many times each id appears in zz (This is easy using COUNT) b. how many times each id appears in sd1 plus sd2. (If a total is not possible, then a separate listing for sd1 and sd2 would suffice (just like we have for zz) c. The final table should list each id number with the following columns: id number total times id number appears in zz column total times id number appears in sd1 & sd2 combined I sure hope this makes sense. Oh, one more thing. I can't change the table structure because I have simplified it here for finding a solution. The above columns are part of a much larger table that is currently in use for other purposes. Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Totals Across Multiple Records
Albert, Your spec isn't entirely clear to me (eg if sd1 & sd2 were 'unique within each record', wouldn't there be zero rows where sd1=sd2?), but is this what you're looking for ... SELECT zz, COUNT(zz) as zzCount, SUM(IF(zz=sd1 AND sd1=sd2, 1, 0)) AS sd1sd2Count FROM ss GROUP BY zz; Peter Brawley http://www.artfulsoftware.com - Albert Padley wrote: I would be grateful if those of you around this weekend could help me figure out if what I'm after is possible. I've already spent hours with the manual, the archives and my books. I've looked at JOINS and TEMP TABLES but still can't come up with a solution. THE ENVIRONMENT: MySQL Version 4.0.24 (so subselects are not available) THE TABLE CREATE TABLE `ss` ( `tt` INT NOT NULL AUTO_INCREMENT , `zz` INT( 3 ) NOT NULL , `sd1` INT( 3 ) NOT NULL , `sd2` INT( 3 ) NOT NULL , PRIMARY KEY ( `id` ) ); THE ISSUE: 1. xx, sd1 and sd2 all contain id numbers. These numbers are unique within each record. 2. I need to scan the table and create a table row for each id number in zz that contains: a. how many times each id appears in zz (This is easy using COUNT) b. how many times each id appears in sd1 plus sd2. (If a total is not possible, then a separate listing for sd1 and sd2 would suffice (just like we have for zz) c. The final table should list each id number with the following columns: id number total times id number appears in zz column total times id number appears in sd1 & sd2 combined I sure hope this makes sense. Oh, one more thing. I can't change the table structure because I have simplified it here for finding a solution. The above columns are part of a much larger table that is currently in use for other purposes. Thanks. Albert Padley -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.9.2/55 - Release Date: 7/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Totals Across Multiple Records
I would be grateful if those of you around this weekend could help me figure out if what I'm after is possible. I've already spent hours with the manual, the archives and my books. I've looked at JOINS and TEMP TABLES but still can't come up with a solution. THE ENVIRONMENT: MySQL Version 4.0.24 (so subselects are not available) THE TABLE CREATE TABLE `ss` ( `tt` INT NOT NULL AUTO_INCREMENT , `zz` INT( 3 ) NOT NULL , `sd1` INT( 3 ) NOT NULL , `sd2` INT( 3 ) NOT NULL , PRIMARY KEY ( `id` ) ); THE ISSUE: 1. xx, sd1 and sd2 all contain id numbers. These numbers are unique within each record. 2. I need to scan the table and create a table row for each id number in zz that contains: a. how many times each id appears in zz (This is easy using COUNT) b. how many times each id appears in sd1 plus sd2. (If a total is not possible, then a separate listing for sd1 and sd2 would suffice (just like we have for zz) c. The final table should list each id number with the following columns: id number total times id number appears in zz column total times id number appears in sd1 & sd2 combined I sure hope this makes sense. Oh, one more thing. I can't change the table structure because I have simplified it here for finding a solution. The above columns are part of a much larger table that is currently in use for other purposes. Thanks. Albert Padley -- 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]
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: > > 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 > > funct
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:
> 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.
> *
> * Given a database query that retur
Re: Cumulative Totals
- Original Message -
From: "Russell Horn" <[EMAIL PROTECTED]>
To:
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.
*
* 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.
*
* For example, given this table:
*
* EMPNOSALARY
* 125000.00
* 218000.00
* 332000.00
*
*
*
* and this query:
* select empno, salary, runningSum(salary) as running_sum from
mytable
*
* The query will return:
*
* EMPNO SALARY RUNNING_SUM
* 1 25000.00 25000.00
* 2 18000.00 43000.00
* 3 32000.00 75000.00
*
*
* 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.
*
* @version 1.0
* @since 2005-03-03
*
* @ret
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
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]
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: problem with totals doubling when using a right join....
You have to look at the intermediate results of this to understand why your
sums are doubling up:
FROM daily_traffic
LEFT JOIN yell_int_traffic
ON daily_traffic.day = yell_int_traffic.day
WHERE daily_traffic.day BETWEEN '2004-05-01' AND '2004-05-31' and
daily_traffic.client in ('yell','yell2002') and daily_traffic.type =
'inline'
I suspect that you are getting twice the number of rows than you expected.
Change your statement so that it runs without the GROUP BY and so that it
doesn't SUM() all of the columns. I'll bet you will not see the data
pattern you thought you had.
Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
The
"Andrew
Braithwaite" To: <[EMAIL PROTECTED]>
<[EMAIL PROTECTED]cc: "Rob Leney" <[EMAIL
PROTECTED]>
com> Fax to:
Subject: problem with totals doubling
when using a right join
06/02/2004 09:26
AM
Hi All,
I have a problem. I have 2 tables:
mysql> SELECT int_traffic.day, sum(int_traffic.deliveries) as deliveries
-> FROM int_traffic
-> WHERE int_traffic.day between '2004-05-01' and '2004-05-31'
-> GROUP BY int_traffic.day
-> ORDER BY int_traffic.day;
+++
| day| deliveries |
+++
| 2004-05-01 | 6 |
| 2004-05-03 | 40 |
| 2004-05-04 |102 |
| 2004-05-05 |103 |
| 2004-05-06 |119 |
| 2004-05-07 | 57 |
| 2004-05-08 | 17 |
| 2004-05-09 | 12 |
| 2004-05-10 | 87 |
| 2004-05-11 | 82 |
| 2004-05-12 | 86 |
| 2004-05-13 | 76 |
| 2004-05-14 | 68 |
| 2004-05-15 | 38 |
| 2004-05-16 | 9 |
| 2004-05-17 |126 |
| 2004-05-18 | 83 |
| 2004-05-19 |149 |
| 2004-05-20 | 57 |
| 2004-05-21 | 76 |
| 2004-05-22 | 18 |
| 2004-05-23 | 7 |
| 2004-05-24 | 99 |
| 2004-05-25 | 78 |
| 2004-05-26 | 56 |
| 2004-05-27 | 16 |
| 2004-05-28 | 8 |
+++
And:
mysql> SELECT daily_traffic.day, sum(daily_traffic.deliveries) as
deliveries
-> FROM daily_traffic
-> WHERE daily_traffic.day between '2004-05-01' and '2004-05-31'
-> GROUP BY daily_traffic.day
-> ORDER BY daily_traffic.day;
+++
| day| deliveries |
+++
| 2004-05-01 | 101936 |
| 2004-05-03 | 96954 |
| 2004-05-04 | 190871 |
| 2004-05-05 | 192995 |
| 2004-05-06 | 243518 |
| 2004-05-07 | 353032 |
| 2004-05-08 | 197238 |
| 2004-05-09 | 92295 |
| 2004-05-10 | 208991 |
| 2004-05-11 | 201924 |
| 2004-05-12 | 206812 |
| 2004-05-13 | 200308 |
| 2004-05-14 | 184485 |
| 2004-05-15 | 100602 |
| 2004-05-16 | 89567 |
| 2004-05-17 | 202828 |
| 2004-05-18 | 196956 |
| 2004-05-19 | 197561 |
| 2004-05-20 | 197962 |
| 2004-05-21 | 194170 |
| 2004-05-22 | 102406 |
| 2004-05-23 | 86801 |
| 2004-05-24 | 201442 |
| 2004-05-25 | 197852 |
| 2004-05-26 | 191453 |
| 2004-05-27 | 185538 |
| 2004-05-28 | 167304 |
| 2004-05-29 | 95079 |
| 2004-05-30 | 80982 |
| 2004-05-31 | 95863 |
+++
But when I do this:
mysql> SELECT distinct daily_traffic.day,
-> sum(daily_traffic.deliveries) as external,
sum(yell_int_traffic.deliveries) as deliveries
-> FROM daily_traffic
-> LEFT JOIN yell_int_traffic
-> ON daily_traffic.day = yell_int_traffic.day
-> WHERE daily_traffic.day BETWEEN '2004-05-01' AND '2004-05-31' and
daily_traffic.client in ('yell','yell2002') and daily_traffic.type =
'inline'
-> GROUP by daily_traffic.day;
++--+--
problem with totals doubling when using a right join....
Hi All,
I have a problem. I have 2 tables:
mysql> SELECT int_traffic.day, sum(int_traffic.deliveries) as deliveries
-> FROM int_traffic
-> WHERE int_traffic.day between '2004-05-01' and '2004-05-31'
-> GROUP BY int_traffic.day
-> ORDER BY int_traffic.day;
+++
| day| deliveries |
+++
| 2004-05-01 | 6 |
| 2004-05-03 | 40 |
| 2004-05-04 |102 |
| 2004-05-05 |103 |
| 2004-05-06 |119 |
| 2004-05-07 | 57 |
| 2004-05-08 | 17 |
| 2004-05-09 | 12 |
| 2004-05-10 | 87 |
| 2004-05-11 | 82 |
| 2004-05-12 | 86 |
| 2004-05-13 | 76 |
| 2004-05-14 | 68 |
| 2004-05-15 | 38 |
| 2004-05-16 | 9 |
| 2004-05-17 |126 |
| 2004-05-18 | 83 |
| 2004-05-19 |149 |
| 2004-05-20 | 57 |
| 2004-05-21 | 76 |
| 2004-05-22 | 18 |
| 2004-05-23 | 7 |
| 2004-05-24 | 99 |
| 2004-05-25 | 78 |
| 2004-05-26 | 56 |
| 2004-05-27 | 16 |
| 2004-05-28 | 8 |
+++
And:
mysql> SELECT daily_traffic.day, sum(daily_traffic.deliveries) as
deliveries
-> FROM daily_traffic
-> WHERE daily_traffic.day between '2004-05-01' and '2004-05-31'
-> GROUP BY daily_traffic.day
-> ORDER BY daily_traffic.day;
+++
| day| deliveries |
+++
| 2004-05-01 | 101936 |
| 2004-05-03 | 96954 |
| 2004-05-04 | 190871 |
| 2004-05-05 | 192995 |
| 2004-05-06 | 243518 |
| 2004-05-07 | 353032 |
| 2004-05-08 | 197238 |
| 2004-05-09 | 92295 |
| 2004-05-10 | 208991 |
| 2004-05-11 | 201924 |
| 2004-05-12 | 206812 |
| 2004-05-13 | 200308 |
| 2004-05-14 | 184485 |
| 2004-05-15 | 100602 |
| 2004-05-16 | 89567 |
| 2004-05-17 | 202828 |
| 2004-05-18 | 196956 |
| 2004-05-19 | 197561 |
| 2004-05-20 | 197962 |
| 2004-05-21 | 194170 |
| 2004-05-22 | 102406 |
| 2004-05-23 | 86801 |
| 2004-05-24 | 201442 |
| 2004-05-25 | 197852 |
| 2004-05-26 | 191453 |
| 2004-05-27 | 185538 |
| 2004-05-28 | 167304 |
| 2004-05-29 | 95079 |
| 2004-05-30 | 80982 |
| 2004-05-31 | 95863 |
+++
But when I do this:
mysql> SELECT distinct daily_traffic.day,
-> sum(daily_traffic.deliveries) as external,
sum(yell_int_traffic.deliveries) as deliveries
-> FROM daily_traffic
-> LEFT JOIN yell_int_traffic
-> ON daily_traffic.day = yell_int_traffic.day
-> WHERE daily_traffic.day BETWEEN '2004-05-01' AND '2004-05-31' and
daily_traffic.client in ('yell','yell2002') and daily_traffic.type =
'inline'
-> GROUP by daily_traffic.day;
++--++
| day| external | deliveries |
++--++
| 2004-05-01 | 203872 | 12 |
| 2004-05-03 | 193908 | 80 |
| 2004-05-04 | 954355 |204 |
| 2004-05-05 | 771980 |206 |
| 2004-05-06 | 974072 |238 |
| 2004-05-07 | 1412128 |114 |
| 2004-05-08 | 394476 | 34 |
| 2004-05-09 | 184590 | 24 |
| 2004-05-10 | 417982 |174 |
| 2004-05-11 | 403848 |164 |
| 2004-05-12 | 413624 |172 |
| 2004-05-13 | 400616 |152 |
| 2004-05-14 | 737940 |136 |
| 2004-05-15 | 201204 | 76 |
| 2004-05-16 | 179134 | 18 |
| 2004-05-17 | 811312 |252 |
| 2004-05-18 | 393912 |166 |
| 2004-05-19 | 395122 |298 |
| 2004-05-20 | 791848 |114 |
| 2004-05-21 | 776680 |152 |
| 2004-05-22 | 204812 | 36 |
| 2004-05-23 | 173602 | 14 |
| 2004-05-24 | 402884 |198 |
| 2004-05-25 | 1187112 |156 |
| 2004-05-26 | 382906 |112 |
| 2004-05-27 | 742152 | 32 |
| 2004-05-28 | 334608 | 16 |
| 2004-05-29 |95079 | NULL |
| 2004-05-30 |80982 | NULL |
| 2004-05-31 |95863 | NULL |
++--++
All the results double for the days where there is data in both tables;
e.g. for the 29th, the total goes from 167304 to 334608 in the left
join. I want to see the same result set format but without the doubled
up numbers.
Does anyone have any ideas how to solve this?
Help will be most appreciated...
Cheers,
Andrew
Sql, query
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Totals
At 10:19 -0800 3/24/04, Craig Gardner wrote: I need to get totals for the data in my database. I stumbled upon the "WITH ROLLUP" modifier (http://www.mysql.com/doc/en/GROUP-BY-Modifiers.html) and it appears to be exactly what I'm looking for, except for the fact that when I try to use it I get the following message: This version of MySQL doesn't yet support 'ROLLUP' The version of mySQL I have installed on this machine is "4.0.18-standard". Is there something I need to install/configure to get this modifier to work properly, or is there another way to get the totals I want. The first sentence on that page states: As of MySQL 4.1.1, the GROUP BY clause allows a WITH ROLLUP modifier that causes extra rows to be added to the summary output. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Totals
I need to get totals for the data in my database. I stumbled upon the "WITH ROLLUP" modifier (http://www.mysql.com/doc/en/GROUP-BY-Modifiers.html) and it appears to be exactly what I'm looking for, except for the fact that when I try to use it I get the following message: This version of MySQL doesn't yet support 'ROLLUP' The version of mySQL I have installed on this machine is "4.0.18-standard". Is there something I need to install/configure to get this modifier to work properly, or is there another way to get the totals I want. Thanks, Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
running totals
Hi Folks, I'm using the following SQL SELECT statement to query a list of labor items. I'm able to compute my number of hours(thanks Paul) and the line amount (hours*rate=lineTotal) and now what I would like to do is to keep a running total for hours and lineTotal. Is there any way I can do that in the following SQL statement? Any ideas? SQL--- SELECT timein,timeout,workdate,rate,description, ROUNDTIME_TO_SEC(timeOut) - TIME_TO_SEC(timeIn))/60)/60),2) AS hours, ROUND(TIME_TO_SEC(timeOut) - TIME_TO_SEC(timeIn))/60)/60) * rate),2) AS lineTotal FROM labor_items WHERE woid='$woid' GROUP BY laborid END Thanks in advance, Todd Schacherl [EMAIL PROTECTED] One of the penalties for refusing to participate in politics is that you end up being governed by your inferiors. -Plato - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: getting totals with data
On Monday, June 17, 2002, at 05:44 PM, Galen Wright-Watson wrote: > Another option is to SELECT INTO a temporary table (or CREATE > TEMPORARY ... > SELECT), then query the temporary table for the total_hits. Would the overhead of generating a temporary table for this query be worthwhile? I'm wondering, since the query will be executed by a PHP script (which of course has a number of other queries that execute along with it). Or would just having a query that strictly returns a COUNT of results, then a separate query returning the actual results (but constrained by LIMIT) be more resource-efficient... I wonder. > The reference to table1.total_hits is correct only if table1 has a > column > called total_hits. You don't need to (and can't) prefix a column alias > by a > table name (I think; a few small experiments seemed to confirm this). This is useful knowledge, I somehow thought that aliased result columns could be treated like regular columns. > I just thought of another possibility if all you want is the number of > matching rows. If you're using an SQL client, it should report the > number > of rows returned. If you're using an API to talk to the server, there > should > be a function to get the number of rows in a query result (e.g. > mysql_num_rows() in the C and PHP APIs). This would be perfect except that it seems that my LIMIT clause (which helps keep the number of results that are handed to the PHP script and turned into HTML low) would yield the LIMITed number of rows, whereas I'm trying to determine how many rows would be returned if I had not used LIMIT (the "total hits" part of "displaying X through X of X total hits"). Thanks for the pointers, Galen. Erik PS: to any who respond to this thread, please CC me as I have temporarily unsubbed the list. Erik Price Web Developer Temp Media Lab, H.H. Brown [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: row totals
> I need to know how many total reviews are in my table for each category > name, im using this query and it doesn't work: > > SELECT SUM(name) AS articletotal WHERE category = 'Processors' Try SELECT count(name) AS articletotal FROM tablename WHERE category = 'Processors' Sincerely, Craig Vincent - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
row totals
Hey Guys, What is the command to add up the amount of rows in a total? I need to know how many total reviews are in my table for each category name, im using this query and it doesn't work: SELECT SUM(name) AS articletotal WHERE category = 'Processors' mysql Thanks! -Alex "Big Al" Behrens E-mail: [EMAIL PROTECTED] Urgent E-mail: [EMAIL PROTECTED] (Please be brief!) Phone: 651-482-8779 Cell: 651-329-4187 Fax: 651-482-1391 ICQ: 3969599 Owner of the 3D-Unlimited Network: http://www.3d-unlimited.com Send News: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Getting totals from database
Eric, Thank you for the information! It works fine! I did go to the page below, but it was for Mathematical EQUATIONS, like syne,radius, circum, etc... I looked under all of those pages and never really saw and reference to simply pulling totals out. I really appreciate it! It works GREAT! == Phillip Ferraro WorldNet Access, Inc [EMAIL PROTECTED]Onslow County's PREMIER InterNet Service Voice (910) 346-0835824 Gumbranch Square, Suite Q FAX (910) 455-1933 Jacksonville, Nc 28540-6269 == On Fri, 26 Jan 2001, Eric Fitzgerald wrote: > http://www.mysql.com/doc/M/a/Mathematical_functions.html > > Please please please read manual before asking questions. > > SELECT SUM(minutes) AS ttltime, user FROM table WHERE user = 'someuser' > GROUP BY user > > - Original Message - > From: <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, January 26, 2001 3:00 PM > Subject: Getting totals from database > > > > > > I know this is probably a simple thing, but what I would like to > > be able to do, is to query a specific account name for minutes each day > > and then have those minutes ADDED up to show the current total time > > online. > > > > My database has all of the info in the table, all I want to do is be > > able to query for a specific user and show total time for the month to > > date. > > > > Any help appreciated! Please reply via separate email.. > > > > > > == > > Phillip Ferraro WorldNet Access, Inc > > [EMAIL PROTECTED] Onslow County's PREMIER InterNet Service > > Voice (910) 346-0835 824 Gumbranch Square, Suite Q > > FAX (910) 455-1933 Jacksonville, Nc 28540-6269 > > > > == > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Getting totals from database
http://www.mysql.com/doc/M/a/Mathematical_functions.html Please please please read manual before asking questions. SELECT SUM(minutes) AS ttltime, user FROM table WHERE user = 'someuser' GROUP BY user - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, January 26, 2001 3:00 PM Subject: Getting totals from database > > I know this is probably a simple thing, but what I would like to > be able to do, is to query a specific account name for minutes each day > and then have those minutes ADDED up to show the current total time > online. > > My database has all of the info in the table, all I want to do is be > able to query for a specific user and show total time for the month to > date. > > Any help appreciated! Please reply via separate email.. > > == > Phillip Ferraro WorldNet Access, Inc > [EMAIL PROTECTED] Onslow County's PREMIER InterNet Service > Voice (910) 346-0835 824 Gumbranch Square, Suite Q > FAX (910) 455-1933 Jacksonville, Nc 28540-6269 > == > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Getting totals from database
I know this is probably a simple thing, but what I would like to be able to do, is to query a specific account name for minutes each day and then have those minutes ADDED up to show the current total time online. My database has all of the info in the table, all I want to do is be able to query for a specific user and show total time for the month to date. Any help appreciated! Please reply via separate email.. == Phillip Ferraro WorldNet Access, Inc [EMAIL PROTECTED]Onslow County's PREMIER InterNet Service Voice (910) 346-0835824 Gumbranch Square, Suite Q FAX (910) 455-1933 Jacksonville, Nc 28540-6269 == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
