Okay, well it turns out that this works exactly how I want/expect it to. The documentation was a bit confusing.
SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (@tv:=(views * ppview)) AS totalviews, (@tc:=(clicks * ppclick)) AS totalclicks, @tv + @tc AS grandtotal FROM advertisements a ORDER BY grandtotal desc; Thanks Jay for your ideas. Daevid. > -----Original Message----- > From: Daevid Vincent [mailto:[EMAIL PROTECTED] > Sent: Monday, April 17, 2006 7:42 PM > To: mysql@lists.mysql.com > Subject: RE: How can I use a value computed in my SQL query > for further computations? > > To add to this, I will also want to be able to "ORDER BY" > those three new > columns (totalviews, totalclicks, grandtotal) as well.. I'm > using mySQL 5 > and innodb tables. > > I saw this page: > http://dev.mysql.com/doc/refman/5.0/en/user-variables.html > > But it says: > " > Note: In a SELECT statement, each expression is evaluated > only when sent to > the client. This means that in a HAVING, GROUP BY, or ORDER > BY clause, you > cannot refer to an expression that involves variables that > are set in the > SELECT list. For example, the following statement does not > work as expected: > > mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5; > " > > So that seems pretty useless for my needs. > > And I'm using this in combination with PHP and Ruby for what > it's worth. > > It seems silly that I would have to use PHP's multisort() to > sort/order data > that I already have in a database, and it seems silly that I > should have to > use PHP to do basic math on the table when mySQL can do it > probably faster. > > > -----Original Message----- > > From: Daevid Vincent [mailto:[EMAIL PROTECTED] > > Sent: Monday, April 17, 2006 7:33 PM > > To: mysql@lists.mysql.com > > Subject: How can I use a value computed in my SQL query for > > further computations? > > > > Here is a paired down version of a query I want to make. How > > can I get the > > "grandtotal" column? I know about the "HAVING" clause, but > > that's only going > > to be good for weeding out rows I don't want. I just want to > > do some basic > > math here. > > > > SELECT a.*, > > DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS > > created_on_format, > > DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS > > timestamp_format, > > > > (views * ppview) AS totalviews, > > (clicks * ppclick) AS totalclicks, > > totalviews + totalclicks AS grandtotal > > FROM advertisements a; > > > > There has got to be a better way than this (which would be a > > colossal waste > > of computing power to recalculate something that was just done!): > > > > SELECT a.*, > > DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS > > created_on_format, > > DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS > > timestamp_format, > > > > (views * ppview) AS totalviews, > > (clicks * ppclick) AS totalclicks, > > ((views * ppview) + (clicks * ppclick)) AS grandtotal > > FROM advertisements a; > > > > > > -- > > 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]