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]
RE: How can I use a value computed in my SQL query for further computations?
[snip] 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; [/snip] Use variables; http://dev.mysql.com/doc/refman/5.1/en/set-statement.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I use a value computed in my SQL query for further computations?
[snip] 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. [/snip] More http://dev.mysql.com/doc/refman/5.1/en/example-user-variables.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I use a value computed in my SQL query for further computations? [solved]
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]
