Have you tried using the WITH ROLLUP option after the group by clause? It seems to me that might give you something close to what
you are looking for.
----- Original Message -----
From: "C.R.Vegelin" <[EMAIL PROTECTED]>
To: "Lars Schwarz" <[EMAIL PROTECTED]>; <mysql@lists.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 ORDER BY clause,
left out in my example because I had no idea this would be the problem.
It works fine with LEFT JOIN and GROUP BY.
However, I need the ORDER BY ...
Any more suggestions to work around ?
Thanks, Cor
----- Original Message -----
From: "Lars Schwarz" <[EMAIL PROTECTED]>
To: <mysql@lists.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)
On 2/6/07, Lars Schwarz <[EMAIL PROTECTED]> wrote:
i suppose this to be working when you leave the group by?
On 2/6/07, C.R.Vegelin <[EMAIL PROTECTED]> wrote:
> 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`
> , ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1`
> , @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum`
> FROM data2006 AS db
> LEFT JOIN mycountries ON db.Country = mycountries.ID
> WHERE ...
> GROUP 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 Oldenburg
T 0441 2171 354 0
F 0441 2171 354 0
M 0179 512 4628
--
Lars Schwarz
Gottorpstrasse 20
26122 Oldenburg
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: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
