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
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 containi
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 c
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 wo
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 operan
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
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),
>-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
> 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 hold
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
d
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 m
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
f
>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
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.
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
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
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 | Nul
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 colu
m 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
ther
d) 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, @cumr
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 PRO
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,
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
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
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
"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 | pay
; >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( `pay
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]
t; 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
> so
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
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 i
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
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
005, 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
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&#
:
> 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
- 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 |
st 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` , &
005-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 | paymentDat
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
y" <[EMAIL
PROTECTED]>
com> Fax to:
Subject: problem
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;
++--
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
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:
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
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'
> 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 Vi
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!
--
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
CTED]>
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 cur
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
51 matches
Mail list logo
