SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE transactions ( ID INT, amount INT, document_id INT ); INSERT INTO transactions VALUES (1, 100.00, 11), (2, 80.00, 12), (3, 120.00, 13); CREATE TABLE bill_details ( ID INT, amount INT, document_id INT, description VARCHAR(64) ); INSERT INTO bill_details VALUES (1, 20.00, 11, 'A'), (2, 60.00, 11, 'B'), (3, 20.00, 11, 'C'), (4, 80.00, 12, 'D'), (5, 60.00, 13, 'E'), (6, 20.00, 13, 'F'), (7, 20.00, 13, 'G'), (8, 40.00, 13, 'H'); SELECT description, /*bill_details.amount, bill_details.document_id, SUM(bill_details.amount) OVER (PARTITION BY document_id ORDER BY bill_details.ID) S, transactions.amount,*/ bill_details.amount + least( transactions.amount - SUM(bill_details.amount) OVER (PARTITION BY document_id ORDER BY bill_details.ID), 0 ) amount FROM bill_details JOIN transactions USING(document_id) ORDER BY bill_details.ID