Create SQL SERVER database

Question

Good afternoon to all! :)

I’m new to programming and I’m having some difficulties

I am trying to create a precedent to update the Cli_totalcompras column, with the total amount already spent by the customer. That is, with the total value of purchases already made, by each customer.

I already selected, and I took the total amount for each client. However, I could not create the precedent to insert this value in the Cli_totalcompras column.

Could someone give me an example of how I can solve this problem?

I think my logic is correct (select this total value from the first customer, make an Insert in the Cli_totalshopping column of that first customer and after that make these steps from the second customer to the last one through a loop)but I cannot reproduce in the code.

I thank all who can help me!

create procedure sp_Cli_TotalCompras
@cli_id int,
as
begin
select c.cli_id, sum(vp.vpr_valorunit)
from vendaProduto vp inner join venda v on vp.ven_ID = v.ven_ID
inner join cliente c on v.cli_ID = c.cli_ID
where c.cli_ID = @cli_id
group by c.cli_id
end

Answer 1

Just join the two commands, it can be done like this:

INSERT INTO NomeDaTabela (cli_id, Cli_TotalCompras)
SELECT c.cli_id, SUM(vp.vpr_valorunit) Cli_TotalCompras
  FROM vendaProduto vp inner join venda v on vp.ven_ID = v.ven_ID
 INNER join cliente c on v.cli_ID = c.cli_ID
 WHERE c.cli_ID = @cli_id
 GROUP by c.cli_id

Just put the correct table name and also the field with the client id, and other fields you need in INSERT

Since it is a precedent, another way would be to store the sum in a variable and use later:

DECLARE @soma float
SET @soma = (SELECT SUM(vp.vpr_valorunit) 
  FROM vendaProduto vp inner join venda v on vp.ven_ID = v.ven_ID
 INNER join cliente c on v.cli_ID = c.cli_ID
 WHERE c.cli_ID = @cli_id
 GROUP by c.cli_id)

INSERT INTO NomeDaTabela (cli_id, Cli_TotalCompras) VALUES (@cli_id, @soma)

In this case, set the type of the variable equal to the field type in INSERT, here I used float to exemplify, but could be decimal or other type.