mysql multiple record set from group by

Given this sample data:

CREATE TABLE payments(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  fromid INT UNSIGNED NOT NULL DEFAULT 0,
  toid INT UNSIGNED NOT NULL DEFAULT 0,
  amount INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);
INSERT INTO payments
(fromid, toid, amount)
VALUES
(1, 2, 10),
(1, 3, 20),
(1, 4, 30),
(2, 4, 10),
(4, 3, 20);

and this query:

SELECT `toid` AS `userid`, 
SUM(`grouped_amount`) AS `balance` 
FROM (
   SELECT `t2`.`toid`, SUM(`t2`.`amount`) AS `grouped_amount`
   FROM (
       SELECT * FROM `payments`
   ) AS `t2` GROUP BY `toid` UNION 
   SELECT `t1`.`fromid`, -SUM(`t1`.`amount`)
   FROM (
       SELECT * FROM `payments`
   )AS `t1` GROUP BY `fromid`)
AS `t` GROUP BY `toid`;

Here is a sqlfiddle with my table, and working result, BUT I'm worried about performance. There are 2 totally identical subqueries. I want to make this in 1 query, is it possible?

So problem is: based on my table, calculate balance of users after all payments.

That's how I'm doing it now: group and sum all payments FROM every user and mark result with - sign. And group and sum all payments TO every user and mark it with + sign. Union this results, and after that sum and group again.

Is there a better way?

Solution

Your query is almost fine, you're just nesting more than necessary. Write it like this:

SELECT userid, SUM(mysum) AS Balance
FROM (

    SELECT
    toid as userid, SUM(amount) as mysum
    FROM payments
    GROUP BY toid

UNION ALL

    SELECT
    fromid, SUM(amount) * -1
    FROM payments
    GROUP BY fromid

) sq
GROUP BY userid;

There's little room for improvement and to meet your requirements you can hardly write it another way. I made it UNION ALL instead of UNION. Difference is, that UNION without ALL implies a DISTINCT which you don't need / want.