A little bit slow but it gets the job done. I used this to generate a report to get the total open_amount for customers.
SELECT s.parent_no, s.parent_name, t.parent_no, t.col_manager, t.invoice_date, t.due_date, t.over_7_date, t.over_7_by_eom, t.open_amount, (SELECT SUM(t2.open_amount) FROM transactions t2 WHERE (t2.parent_no=t.parent_no) AND t2.trans_status = 'open') AS total FROM transactions t LEFT JOIN summary s ON s.parent_no = t.parent_no WHERE ( t.col_manager='aaa' OR t.col_manager = 'bbb' OR t.col_manager = 'ccc' OR t.col_manager = 'ddd' OR t.col_manager = 'eee' OR t.col_manager = 'fff' ) AND t.trans_status = 'open' ORDER BY s.parent_name ASC |