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

[Andrew Presley]

Not sure on how exactly variables work in MySQL but I do know that according 
to ANSI SQL group bys are done before other things in the query.  So your 
query would perform the group by then it would do the actual select.  This 
could be one reason for strange results.

Thanks,
Andrew

From: "Vadim P." <[EMAIL PROTECTED]>
To: Emmett Bishop <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
Subject: Re: User variables + SUM + GROUP BY  = strange behavior
Date: Fri, 16 Apr 2004 05:50:12 -0400
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]

_________________________________________________________________
Get rid of annoying pop-up ads with the new MSN Toolbar – FREE! 
http://toolbar.msn.com/go/onm00200414ave/direct/01/

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