|
|
Posted by smorrey on 10/26/05 20:40
Woohoo! That almost worked, but this actually does!
SELECT (
deposits - withdrawls
) AS balance
FROM (
SELECT (
SELECT sum( to_amount ) AS amount
FROM transactions
WHERE to_user = '1'
GROUP BY to_user
) AS deposits, (
SELECT sum( from_amount ) AS amount
FROM transactions
WHERE from_user = '1'
GROUP BY from_user
) AS withdrawls
) AS result_table
Thank you VERY much, the difference is that the from_amount and
to_amount can and will tend to be very different, due to a fee which is
extracted in some cases, in some types of transactions. But no bother,
once I seen how to do it, it made all the difference.
Thank you!
Justin Koivisto wrote:
> 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?
> >
>
> check your mysql version, sub-queries (select inside select) are still
> relatively new (v4.1+)...
>
> SELECT
> (deposits - withdrawls) as balance
> FROM
> (
> SELECT
> (
> SELECT sum(amount) as amount
> FROM transactions
> WHERE to_user = '1'
> GROUP BY to_user
> ) as deposits,
> (
> SELECT sum(amount) as amount
> FROM transactions
> WHERE from_user = '1'
> GROUP BY from_user
> ) as withdrawls
> ) as result_table
>
> You may need to edit a little as I didn't use the from_amount since I
> was assuming that the amount of the transaction would be equal for the
> to and from sides... transactions table would have the following fields:
>
> id, from_user, to_user, amount
>
> HTH
>
> --
> Justin Koivisto, ZCE - justin@koivi.com
> http://koivi.com
Navigation:
[Reply to this message]
|