Reputation: 7061

Calculating SUMs in MySQL with joins

Background

I my database I have a table filled with items. Each item can have a series of payments.
Once a user has made a payment the user can then use that money by redeeming it. Money can partially be redeemed.

Problem

For each redemption I want to calculate the remaining money. This is equal to doing SUM(paid_amount) - SUM(previous_redemptions) - this_redemption.

The problem is that each payment will be counted many times if there are multiple previous redemptions due to the nature of SQL joins (one row be combination of the joins).

I'm not actually sure what I want can be calculated using only MySQL (and still be reasonably fast).

The problem would be solved if I could somehow make each SQL group contain only a single row with multiple values for each column, something I don't think SQL supports.

The tables

mysql> DESCRIBE items;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

mysql> DESCRIBE payments;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| id          | int(11) | NO   | PRI | NULL    |       |
| item_id     | int(11) | NO   |     | NULL    |       |
| paid_amount | int(11) | NO   |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+

mysql> DESCRIBE redemptions;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| id          | int(11)  | NO   | PRI | NULL    |       |
| item_id     | int(11)  | NO   |     | NULL    |       |
| amount      | int(11)  | YES  |     | NULL    |       |
| create_time | datetime | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+

The data

mysql> SELECT * FROM items;
+----+
| id |
+----+
|  1 |
+----+

mysql> SELECT * FROM payments;
+----+---------+-------------+
| id | item_id | paid_amount |
+----+---------+-------------+
|  1 |       1 |          50 |
|  2 |       1 |          50 |
+----+---------+-------------+

mysql> SELECT * FROM redemptions;
+----+---------+--------+---------------------+
| id | item_id | amount | create_time         |
+----+---------+--------+---------------------+
|  1 |       1 |     10 | 2013-01-01 00:00:00 |
|  2 |       1 |     10 | 2013-01-01 00:01:00 |
|  3 |       1 |     10 | 2013-01-01 00:02:00 |
+----+---------+--------+---------------------+

The query

mysql> SELECT
    ->     redemptions.id AS redemption_id,
    ->     SUM(payments.paid_amount) - COALESCE(SUM(previous_redemptions.amount), 0) - redemptions.amount AS remaining_balance
    -> FROM redemptions
    -> JOIN payments ON payments.item_id = redemptions.item_id
    -> LEFT JOIN redemptions AS previous_redemptions
    -> ON
    ->     previous_redemptions.item_id = redemptions.item_id AND
    ->     previous_redemptions.create_time < redemptions.create_time
    -> GROUP BY redemptions.id;
+---------------+-------------------+
| redemption_id | remaining_balance |
+---------------+-------------------+
|             1 |                90 |
|             2 |                70 |
|             3 |               150 |
+---------------+-------------------+

As you can see this did not really behave as I wanted. I want the remaining_balance for redemption 3 to be 70.

Size of the tables

Items - Several million rows
Payments - Several million rows
Redemptions - Several million rows

This means that making a subquery to first calculate all the used amount (SUM of the previous redemptions) per redemption is out of the question.

MySQL commands for those who want to follow along at home

CREATE TABLE items (
    id int(11) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE payments (
    id int(11) NOT NULL,
    item_id int(11) NOT NULL,
    paid_amount int(11) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE redemptions (
    id int(11) NOT NULL,
    item_id int(11) NOT NULL,
    amount int(11),
    create_time datetime,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO items VALUES (1);

INSERT INTO payments VALUES
    (1, 1, 50),
    (2, 1, 50);

INSERT INTO redemptions VALUES
    (1, 1, 10, '2013-01-01 00:00:00'),
    (2, 1, 10, '2013-01-01 00:01:00'),
    (3, 1, 10, '2013-01-01 00:02:00');

SELECT 
    redemptions.id AS redemption_id,
    SUM(payments.paid_amount) - COALESCE(SUM(previous_redemptions.amount), 0) - redemptions.amount AS remaining_balance
FROM redemptions
JOIN payments ON payments.item_id = redemptions.item_id
LEFT JOIN redemptions AS previous_redemptions
ON
    previous_redemptions.item_id = redemptions.item_id AND 
    previous_redemptions.create_time < redemptions.create_time
GROUP BY redemptions.id;

Upvotes: 3

Answers (3)

Jonathan S.

Reputation: 2228

Given the multiple payment and multiple redemption issue, the best I can come up with would be this:

SELECT 
    redemptions.id AS redemption_id,
    payment_totals.paid_amount - SUM(cumulative_redemptions.amount) AS remaining_balance
FROM redemptions
INNER JOIN
    (SELECT item_id, SUM(paid_amount) paid_amount FROM payments GROUP BY item_id) payment_totals
ON
    redemptions.item_id = payment_totals.item_id
INNER JOIN redemptions AS cumulative_redemptions
ON
    cumulative_redemptions.item_id = redemptions.item_id AND 
    cumulative_redemptions.create_time <= redemptions.create_time
GROUP BY redemptions.id, payment_totals.paid_amount;

SQL Fiddle

This uses a subquery for total payments per item, which wasn't explicitly disallowed in your question, but I make no guarantees on performance. Since you mentioned batch updating in your comment to sgeddes, another option would be to create an actual payment_totals table complete with indexes and repopulate it before running the rest of the batch.

Upvotes: 1

hodl

Reputation: 1420

Here ya go!

SELECT 
    redemptions.id AS redemption_id,
    (payments.paid_amount - SUM(previous_redemptions.amount)) AS remaining_balance
FROM redemptions
JOIN payments ON payments.id = redemptions.payment_id
LEFT JOIN redemptions AS previous_redemptions
ON
    previous_redemptions.payment_id = redemptions.payment_id AND 
    previous_redemptions.id <= redemptions.id
GROUP BY redemptions.id;

SQL Fiddle

Upvotes: 2

sgeddes

Reputation: 62831

This uses a user-defined variable to keep a running total:

  SELECT 
    r.id AS redemption_id,
    @totAmt:=SUM(p.paid_amount)-COALESCE(@sumAmt,r.amount) totAmt,
    @sumAmt:=COALESCE(@sumAmt,r.amount)+r.amount
  FROM redemptions r
    JOIN payments p ON p.id = r.payment_id
    JOIN (SELECT @sumAmt:=NULL, @totAmt:=0) s
  GROUP BY r.id
  ORDER BY r.create_time

SQL Fiddle Demo

EDIT: Given comments, you could use a correlated subquery:

  SELECT 
    r.id AS redemption_id,
    SUM(p.paid_amount)-
      (SELECT SUM(r2.amount)
       FROM redemptions r2
       WHERE r.item_id = r2.item_id AND 
          r2.create_time <= r.create_time )
  FROM redemptions r
    JOIN payments p ON p.item_id = r.item_id
  GROUP BY r.id
  ORDER BY r.create_time

Updated SQL Fiddle