CODEWITHSUNDEEP
mysqlsql
starleaf1
starleaf1

Reputation: 2866

Selecting related rows in MySQL

Let me elaborate. I have a table like this (updated to include more example)

| id | date      | cust | label                   | paid | due   |
+----+-----------+------+-------------------------+------+-------+
|  1 |2016-02-02 |    1 | SALE: Acme Golf Balls   |    0 |  1000 |
| 20 |2016-03-01 |    1 | PAYMENT: transaction #1 |  700 |     0 |
| 29 |2016-03-02 |    1 | PAYMENT: transaction #1 |  300 |     0 |
| 30 |2016-03-02 |    3 | SALE: Acme Large Anvil  |  500 |   700 |
| 32 |2016-03-02 |    3 | PAYMENT: transaction #30|  100 |     0 |
| 33 |2016-03-03 |    2 | SALE: Acme Rockets      |    0 |  2000 |

Now I need to output a table that displays sales that haven't been paid in full and the remaining amount. How do I do that? There's not much info out there on how to relate rows from the same table.

EDIT: Here's the output table I'm thinking of making

Table: debts_n_loans
| cust | label                  | amount |
==========================================
|    3 | SALE: Acme Large Anvil |    100 |
|    2 | SALE: Acme Rockets     |   2000 |

Upvotes: 0

Views: 34

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If cust is the key that ties them together, then you can just use aggregation and a having clause:

select cust, sum(paid), sum(due)
from t
group by cust
having sum(paid) <> sum(due);

If you want the details, you can use a join, in or exists to get the details.

EDIT:

If you need to do this using the transaction at the end of the string:

select t.id, t.due, sum(tpay.paid) as paid
from t left join
     t tpay
     on tpay.label like '%#' || t.id
where t.label like 'SALE:%' and
      tpay.label like 'PAYMENT:%'
group by t.id, t.due
having t.due <> sum(tpay.paid);

Upvotes: 1

Dylan Su
Dylan Su

Reputation: 6065

Try this:

SELECT 
    cust, 
    SUM(due) - SUM(paid) AS remaining
FROM t1
GROUP BY cust
HAVING SUM(due) > SUM(paid);

Upvotes: 0

Varon
Varon

Reputation: 3916

So you only need the rows with a due greater than 0

SELECT * FROM <table> WHERE due > 0;

Upvotes: 0

Related Questions