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: [email protected]
> 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: [email protected]
> > 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]
