CODEWITHSUNDEEP
phpmysql
Jahangir Hossain
Jahangir Hossain

Reputation: 27

How to insert SUM of columns of one table as value of the column of another table in mysql?

I have two tables named 'register' and 'customer'. The register table looks like:

id     customer_id     purchase     paid     discount     return     previous_due
1      97              500          200      50                      100
2      98              1500         700      150                     500
3      97                                                 70
4      99              900          900      0                       1000
5      98                           200
6      99              1200         1000

I want the SUM of each column by customer_id and automatically update the columns of 'customer' table. 'customer' table looks like:

customer_id  tot_pur  tot_paid  tot_disc  tot_return  tot_due  final_due
97           500      200       50        0           100      350
98
99

final_due column will be calculated like (tot_pur + tot_due) - (tot_paid + tot_disc + tot_return)

I am not good at mysql, so best and easy way will save me. Any help is appreciated. Thanks in advance.

Upvotes: 1

Views: 60

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522211

Honestly, unless you need these sums to be on tap in a lightning-fast way, I might suggest just storing the values of each transaction separately, and then creating a view which aggregates by customer and finds the sums:

CREATE VIEW register_sums AS (
    SELECT
        customer_id,
        SUM(purchase) AS tot_pur,
        SUM(paid) AS tot_paid,
        SUM(discount) AS tot_disc, 
        SUM(return) AS tot_return,
        SUM(previous_due) AS tot_due,
        SUM(purchase) + SUM(paid) - SUM(discount) - SUM(return) -
            SUM(previous_due) AS final_due
    FROM register
    GROUP BY customer_id
)

Upvotes: 1

Related Questions