List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 18 2006 2:41am
Subject:RE: How can I use a value computed in my SQL query for further computations?
View as plain text  
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:daevid@stripped] 
> Sent: Monday, April 17, 2006 7:33 PM
> To: mysql@stripped
> 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/mysql?unsub=1
> 
> 

Thread
How can I use a value computed in my SQL query for further computations?Daevid Vincent18 Apr
RE: How can I use a value computed in my SQL query for further computations?Daevid Vincent18 Apr
RE: How can I use a value computed in my SQL query for further computations?Jay Blanchard18 Apr
RE: How can I use a value computed in my SQL query for further computations?Jay Blanchard18 Apr