i suppose this to be working when you leave the group by?

2/6/07, C.R.Vegelin <cr.vegelin (AT) hetnet (DOT) nlwrote:
I want to calc a running sum with @variables.
Using the command line client, I enter:
SET @row := 0, @runsum := 0;
followed by:
SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country`
, RUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1`
, @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum`
FRM data2006 AS db
LEFT JIN mycountries N db.Country = mycountries.ID
WHERE
GRUP BY db.Country;
--
Results are:
Row Country Q1 RunSum
1 Germany 90 90
2 France 60 60
3 Norway 24 24
etc.

I expect the RunSum for Germany 60, France 150, Norway 174 etc.
Whay am I doing wrong ? Any help is appreciated !

Regards, Cor
--

oops, sorry, forget the last post i made. it's not related to the
group by. it should work like that from my point of view. a stripped
down version of this works for me (tried without the left join)

2/6/07, Lars Schwarz <lars.schwarz (AT) gmail (DOT) comwrote:
i suppose this to be working when you leave the group by?

2/6/07, C.R.Vegelin <cr.vegelin (AT) hetnet (DOT) nlwrote:
I want to calc a running sum with @variables.
Using the command line client, I enter:
SET @row := 0, @runsum := 0;
followed by:
SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country`
, RUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1`
, @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum`
FRM data2006 AS db
LEFT JIN mycountries N db.Country = mycountries.ID
WHERE
GRUP BY db.Country;
--
Results are:
Row Country Q1 RunSum
1 Germany 90 90
2 France 60 60
3 Norway 24 24
etc.

I expect the RunSum for Germany 60, France 150, Norway 174 etc.
Whay am I doing wrong ? Any help is appreciated !

Regards, Cor
>
>
>
>

Thanks Lars, Dusan,

I found out that the problem is caused by an RDER BY clause,
left out in my example because I had no idea this would be the problem.
It works fine with LEFT JIN and GRUP BY.
However, I need the RDER BY
Any more suggestions to work around ?

Thanks, Cor

Message
From: "Lars Schwarz" <lars.schwarz (AT) gmail (DOT) com>
To: <mysql (AT) lists (DOT) mysql.com>
Sent: Tuesday, February 06, 2007 12:58 PM
Subject: Re: running sum with a @variable

oops, sorry, forget the last post i made. it's not related to the
group by. it should work like that from my point of view. a stripped
down version of this works for me (tried without the left join)

2/6/07, Lars Schwarz <lars.schwarz (AT) gmail (DOT) comwrote:
>i suppose this to be working when you leave the group by?
>>
>2/6/07, C.R.Vegelin <cr.vegelin (AT) hetnet (DOT) nlwrote:
>I want to calc a running sum with @variables.
>Using the command line client, I enter:
>SET @row := 0, @runsum := 0;
>followed by:
>SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country`
>, RUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1`
>, @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum`
>FRM data2006 AS db
>LEFT JIN mycountries N db.Country = mycountries.ID
>WHERE
>GRUP BY db.Country;
>>
>>
>Results are:
>Row Country Q1 RunSum
>1 Germany 90 90
>2 France 60 60
>3 Norway 24 24
>etc.
>>
>I expect the RunSum for Germany 60, France 150, Norway 174 etc.
>Whay am I doing wrong ? Any help is appreciated !
>>
>Regards, Cor
>>
>>
>>
>>
>--
>Lars Schwarz
>Gottorpstrasse 20
>26122
>T 0441 2171 354 0
>F 0441 2171 354 0
>M 0179 512 4628
>>
>
>

Have you tried using the WITH RLLUP option after the group by clause? It seems to me that might give you something close to what
you are looking for.

Message
From: "C.R.Vegelin" <cr.vegelin (AT) hetnet (DOT) nl>
To: "Lars Schwarz" <lars.schwarz (AT) gmail (DOT) com>; <mysql (AT) lists (DOT) mysql.com>
Sent: Tuesday, February 06, 2007 9:45 AM
Subject: Re: running sum with a @variable

Thanks Lars, Dusan,

I found out that the problem is caused by an RDER BY clause,
left out in my example because I had no idea this would be the problem.
It works fine with LEFT JIN and GRUP BY.
However, I need the RDER BY
Any more suggestions to work around ?

Thanks, Cor
--
Message
From: "Lars Schwarz" <lars.schwarz (AT) gmail (DOT) com>
To: <mysql (AT) lists (DOT) mysql.com>
Sent: Tuesday, February 06, 2007 12:58 PM
Subject: Re: running sum with a @variable
>
>
>oops, sorry, forget the last post i made. it's not related to the
>group by. it should work like that from my point of view. a stripped
>down version of this works for me (tried without the left join)
>>
>2/6/07, Lars Schwarz <lars.schwarz (AT) gmail (DOT) comwrote:
i suppose this to be working when you leave the group by?

2/6/07, C.R.Vegelin <cr.vegelin (AT) hetnet (DOT) nlwrote:
I want to calc a running sum with @variables.
Using the command line client, I enter:
SET @row := 0, @runsum := 0;
followed by:
SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country`
, RUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1`
, @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum`
FRM data2006 AS db
LEFT JIN mycountries N db.Country = mycountries.ID
WHERE
GRUP BY db.Country;
>
>
Results are:
Row Country Q1 RunSum
1 Germany 90 90
2 France 60 60
3 Norway 24 24
etc.
>
I expect the RunSum for Germany 60, France 150, Norway 174 etc.
Whay am I doing wrong ? Any help is appreciated !
>
Regards, Cor
>
>

--
Lars Schwarz
Gottorpstrasse 20
26122
T 0441 2171 354 0
F 0441 2171 354 0
M 0179 512 4628

>>
>>
>--
>Lars Schwarz
>Gottorpstrasse 20
>26122
>T 0441 2171 354 0
>F 0441 2171 354 0
>M 0179 512 4628
>>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: @hetnet.nl
>>
>>
>
>
>

I have run into that bug with mysql 5.0.24a: bug #16979 auto-inc

My application was running K on 4.1.7. But after migration to 5, the
problem started.

My questions are:

1-Can I run the patch on 5.0.24a even if it is for 5.0.21.
2-Which patch finally is the good one : the one from [13 May 2006 6:34]
Vadim Tkachenko pa4.diff?
3-I have looked in the manual for "how to apply a patch" but could not
find anything on that topic. But it seems that we can use the solaris
"patch" program?

Thank you in advance.

list of patches:
[4 Feb 2006 22:07] Vadim Tkachenko
auto_inc patch
Attachment: auto_inc.diff (text/plain), 9545 bytes.

[8 May 2006 7:34] Vadim Tkachenko
Patch for 5.0.21 tree
Attachment: auto_inc_5021.diff (application/octet-stream), 10781 bytes.

[8 May 2006 7:35] Vadim Tkachenko
Hi,
I added patch for 5.0.21 tree.
To apply: patch -p1 < auto_inc_5021.diff in 5.0.21 directory

[13 May 2006 6:34] Vadim Tkachenko
new version of patch
Attachment: pa4.diff (text/plain), 11796 bytes.

[2 Aug 2006 17:41] Vadim Tkachenko
Patch for 5.1
Attachment: patch.51.autoinc.diff (text/plain), 10950 bytes.