Re: User variables + SUM + GROUP BY = strange behavior
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]
RE: Learner Here Getting Frustraighted
For starters look at pet.petName=petcolor.petName Try changing it to. pet.petName=color.petName You are referencing the color table as petcolor. Thanks, Andrew From: Barry . [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Learner Here Getting Frustraighted Date: Thu, 08 Apr 2004 14:18:10 + Hi I recently began to work through the Book entitled PHP MySQL For Dummies and i am currently stuck towards the end of the forth chapter ive done everything as said so in the book but i keep getting an error message. Here is an outline of the problem: i have 2 tables one pet containing petName and petType other table is color containgin petName and petColor. The code which i keep getting errors on is: Select * from pet outer join color using (pet.petName=petcolor.petName) ; Now Ive posted my problem on several forums on the internet and have a wide range to replys suggesting what to do but nothing seems to work. Can anyone please help me?? Barry Smith _ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Free up your inbox with MSN Hotmail Extra Storage! Multiple plans available. http://join.msn.com/?pgmarket=en-uspage=hotmail/es2ST=1/go/onm00200362ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: autoincrement column
Keep in mind you can't use truncate in a transaction though. I experienced this problem recently when wanting to delete an entire table and reset my auto increment values. While keeping it in a transaction. From: Dathan Vance Pattishall [EMAIL PROTECTED] To: 'Arthur Radulescu' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: autoincrement column Date: Thu, 8 Apr 2004 12:50:58 -0700 Try truncate table. It essentially drops the table and recreates it. -- DVP -Original Message- From: Arthur Radulescu [mailto:[EMAIL PROTECTED] Sent: Thursday, April 08, 2004 10:36 AM To: [EMAIL PROTECTED] Subject: autoincrement column Before switching to version 4.0.18 of MySQL the command delete from table was also reseting the autonincrement column value... It seems that this is not happening anymore after we have installed this version... Any idee about this problem? Is there any other way to reset this? Arthur -- 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] _ Limited-time offer: Fast, reliable MSN 9 Dial-up Internet access FREE for 2 months! http://join.msn.com/?page=dept/dialuppgmarket=en-usST=1/go/onm00200361ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
