CODEWITHSUNDEEP
mysqljoinsum
NeverendeR
NeverendeR

Reputation: 47

MySQL getting sum of tables with the same id

I have four tables person,loan,ca,payments

I would like to get the sum of all payments amounts and cash advance amounts which has the same ID as the loan joined with a person from a specific date.

Here is my code, but the sum is calculated incorrectly:

SELECT pd.*,
    l.total_loan_amount,
    sum(c.ca_total_amount) AS ctot,
    sum(p.payment_amount)
FROM personal_data pd
LEFT JOIN loans l
    ON pd.id_personal_data = l.id_personal_data
LEFT JOIN ca c
    ON l.id_loan = c.id_loan
LEFT JOIN payments p
    ON l.id_loan = p.id_loan
WHERE l.loan_date = curDate()
    AND (
        c.ca_date = curDate()
        OR c.ca_date IS NULL
        )
    AND (
        p.payment_date = curDate()
        OR p.payment_date IS NULL
        )
GROUP BY pd.id_personal_data

Upvotes: 2

Views: 482

Answers (1)

John Woo
John Woo

Reputation: 263723

Doing that may sometimes retrieve invalid results because id may or may not sometimes be present on other table.

Try using a subquery for each column you want to retrieve.

SELECT  pd.*,
        l.total_loan_amount,
        c.totalCA,
        p.totalPayment
FROM    personal_data pd
        LEFT JOIN loans l
            ON pd.id_personal_data = l.id_personal_data
        LEFT JOIN
        (
            SELECT  id_loan, SUM(ca_total_amount) totalCA
            FROM    ca 
        --  WHERE   DATE(ca_date) = DATE(CURDATE()) OR
        --          ca_date IS NULL
            GROUP BY id_loan
        ) c ON l.id_loan = c.id_loan
        LEFT JOIN
        (
            SELECT  id_loan, SUM(payment_amount) totalPayment
            FROM    payments 
        --  WHERE   DATE(payment_date) = DATE(CURDATE()) OR
        --          payment_date IS NULL
            GROUP BY id_loan
        ) p ON l.id_loan = p.id_loan
WHERE DATE(l.loan_date) = DATE(curDate())

I think dates on every payment and cash advance are irrelevant because you are looking for its totals based on the date of loan

Upvotes: 2

Related Questions