sum aus beiden MySQL-Tabellenspalten
Ich habe zwei MySQL-Tabellen:
table: loan
--------------------------------------------------------
id | advance_id | loan_amount | deposit_amount | date
--------------------------------------------------------
1 | 22556678 | 5000 | 0 | 2015-02-06
2 | 22556678 | 5000 | 0 | 2015-02-07
--------------------------------------------------------
table: advance
--------------------------------------------------------
id | advance_id | advance_amount | purpose | date
--------------------------------------------------------
1 | 22556678 | 20000 | purchase | 2015-01-30
2 | 22556678 | 10000 | purchase | 2015-01-31
Ich möchte die Summe von 'loan_amount' aus der Tabelle: loan und die Summe von 'advance_amount' aus der Tabelle: advance in einer von INNER JOIN verknüpften mysql-Abfrage erhalten.
Wie erhält man die Summe aus den Spalten zweier Tabellen?
SELECT
a.advance_id,
SUM(a.advance_amount) AS adv_amount,
a.purpose,
a.date,
SUM(l.loan_amount) AS loan_amount
FROM
advance AS a
INNER JOIN loan AS l
ON a.advance_id = l.advance_id
GROUP BY a.advance_id
HAVING SUM(l.loan_amount) - SUM(l.deposit_amount) > 0