Re: User variables + SUM + GROUP BY = strange behavior

[Vadim P.]

well, it seems to be fine without SUM and GROUP BY...

E.g.,
  
   "SELECT @a:=Charge, @b:=Cost, @[EMAIL PROTECTED] as Margin ..."

produces expected results.

Emmett Bishop wrote:

Vadim,

if I'm not mistaken, you can't set a variable then use
it in the same statement. 

See http://dev.mysql.com/doc/mysql/en/Variables.html

A little ways down the page...
"The general rule is to never assign and use the same
variable in the same statement."
-- Tripp

--- "Vadim P." <[EMAIL PROTECTED]> wrote:
 

Sorry, the message got garbled, here is a more
digestible look:
-----Original Message-----

Hello all,

Could anyone comment on User Variable behavior in
the example below?
Thanks,
Vadim.
   

=========================================================================
 

mysql> SELECT
   ->        LEFT(CallTime,10) AS CallDate,
   ->        @a := SUM(Charge),
   ->        @b := SUM(Cost),
   ->        @a - @b,
   ->        @a,
   ->        @b
   -> FROM Calls
   -> GROUP by CallDate
   -> ORDER BY CallDate DESC;
   

+------------+------------------+----------------+---------+--------+-----
 

| CallDate   | @a:= SUM(Charge) | @b:= SUM(Cost) |
@a - @b | @a     | @b
   

+------------+------------------+----------------+---------+--------+-----
 

...
| 2004-03-01 |          621.059 |        249.310 | 
30.882 | 39.512 | 8.63
| 2004-02-29 |           54.620 |         17.660 | 
30.882 | 39.512 | 8.63
| 2004-02-28 |          205.581 |         17.460 | 
30.882 | 39.512 | 8.63
| 2004-02-27 |          622.282 |        248.920 | 
30.882 | 39.512 | 8.63
| 2004-02-26 |          607.274 |        277.100 | 
30.882 | 39.512 | 8.63
| 2004-02-25 |          709.698 |        308.580 | 
30.882 | 39.512 | 8.63
| 2004-02-24 |          783.210 |        298.560 | 
30.882 | 39.512 | 8.63
| 2004-02-23 |          799.764 |        252.890 | 
30.882 | 39.512 | 8.63
...



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:   

   

http://lists.mysql.com/[EMAIL PROTECTED]
 



    
                
__________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html
 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]