List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 18 2006 2:58am
Subject:RE: How can I use a value computed in my SQL query for further computations? [solved]
View as plain text  
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:daevid@stripped] 
> Sent: Monday, April 17, 2006 7:42 PM
> To: mysql@stripped
> 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: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
> > 
> > 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
> 
> 

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