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]
