|
|
Posted by smorrey on 10/26/05 20:17
Just a note I'ld thought I'ld mention.
The other logical solution is this.
SELECT deposits - withdrawls as balance FROM(
(SELECT sum(from_amount ) as withdrawls FROM transactions WHERE
from_user = '1' UNION
SELECT sum(to_amount) as deposits FROM transactions WHERE to_user =
'1' )
Which produces the following error
#1248 - Every derived table must have its own alias
I'm going into a corner to cry now.
smorrey@gmail.com wrote:
> Hello all, this might better be suited for the MySQL newsgroup, but I
> figured I'ld post here and see if anyone can help me.
>
> I'm trying to create a simple transaction handling system where users
> pay eachother via points of a sort.
> I have a table where each transaction is stored, in a single row.
> It looks like this
>
> transid,from_user,from_amount,to_user,to_amount
>
> Given the above, I just want a simple single SQL statement that can
> return a full balance.
> Now originally I did this in PHP by querying the DB with the following.
>
> $query[0] = "SELECT SUM(to_amount) WHERE to_user = '$user->uid'";
> $query[1] = "SELECT SUM(from_amount) WHERE from_user = '$user->uid'";
> $deposits = mysql_result(mysql_query($query[0]);
> $withdrawls = mysql_result(mysql_query($query[1]);
> $balance = $deopsits - $withdrawls;
>
> This works great, so I populated the DB with over 100,000 records and
> now page loads take 75-80 seconds for the total balance sheet page (I
> just loop through each user, and there are 100 users), and upwards of
> 10 seconds on a single user query.
>
> After analyzing the code in the profiler (I'm using Zend), I found the
> largest optimization I could make would be to cut out the 2 queries
> variable assignment and simple math (basically all of the code above),
> and move it into a single SQL statement that does all the math.
>
> This SHOULD be elementary, but unfortunately, MySQL doesn't like my
> solution, and searching everywhere I could, I can't seem to find where
> anything similar has every been tried. But come on, I know I can't be
> the first person in history with a similar need for a query like this.
>
> Here is the most elegant solution I could come up with, in all it's raw
> SQL glory.
>
> SELECT SUM(to_amount - from_amount) as balance FROM
> ((SELECT from_amount as withdrawls FROM transactions WHERE from_user =
> '1')
> (SELECT to_amount as deposits FROM transactions WHERE to_user = '1'));
>
> Sadly, even though by every measure I can find it SHOULD work, it just
> doesn't.
> I keep getting
>
> #1064 - You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near '(SELECT to_amount as deposits FROM transactions WHERE to_user =
> '1'))' at line 3
>
> I'm just not seeing it, as far as I can tell this should work.
> Any Ideas?
Navigation:
[Reply to this message]
|