Update Total Accumulated

Question

Update Total Accumulated

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I need to update the cumulative sales of all the clients in the accumPurchases field, for which I try to add all the invoices of each one and record this value there

UPDATE contacts 
SET 
    accumPurchases = (SELECT 
            SUM(inv_total) AS sum_of_inv_total
        FROM
            invoice_master
        WHERE
            invoice_master.inv_cntId = contacts.cnt_id)

but I can not get it to work ...

Error Code: 1048. Column 'accumPurchases' cannot be null

Thanks for your guidance.

mysql sql pdo

asked by Juan Carlos 28.12.2018 в 19:52

source

2 answers

Convert columns of a table into rows Summation variables in java

score 2 · Answer 1

It seems that not all contacts have an invoice. You must decide if, in those cases, it is more convenient that the accumulated sales be null (modifying the properties of the field so that it admits them) or if you prefer the value to be zero (after all) , if there is no invoice, the accumulated sales is zero, right?).

In the latter case, you can use a function type ISNULL to change the null by zeros.

In addition, two things must be taken into account if the query is made in this way:

You can not historicize the invoice table. If at some point you decide to delete old records from that table, the accumulated sales of the contacts would decrease (?).

An index for inv_cntId in the invoice_master is essential so that the query does not become longer when the number of records in the table increases.

score 0 · Answer 2

Changing the accumPurchases field in the table so that it receives nulls implies that you have to validate the value sent to us by the database every time we request it, but it is not really complicated and it works well.

ALTER TABLE 'contacts' CHANGE 'accumPurchases ' 'accumPurchases ' DECIMAL(12,2) NULL;