Create COLUNM with results of an account made within a ROW - MySql

2

Probably the title of this question is not correct, the case is that I would not know how to explain my doubt without an example.

I have a scenario where I need to add the values of products that have not yet been delivered, eg:

My Records:
+ ----------- + ------ + ------------------ + ----------- ------- +
| Products | Qty. | QtDownload | ValueUnit |
+ ----------- + ------ + ------------------ + ----------- ------- +
|. Box .. | .. 5 .. | ... | ... 2 | ..... 30,00 ....... | + ----------- + ------ + ------------------ + ----------- ------- +
| ... Ball ... | .. 8 .. | .......... 4 ......... | ..... 50,00 ....... | + ----------- + ------ + ------------------ + ----------- ------- +

I would like you to display:
+ ----------- + ------ + ------------------ + ----------- ------- + -------------------- +
| Products | Qty. | QtDownload | ValueUnit | ValorPendente

+ ----------- + ------ + ------------------ + ----------- ------- + -------------------- +
|. Box .. | .. 5 .. | ... | ... 2 | ..... 30.00 ....... | 90.00 ......... | + ----------- + ------ + ------------------ + ----------- ------- + -------------------- +
| ... Ball ... | .. 8 .. | .......... 4 ......... | ..... 50.00 ....... | 200.00 ....... | + ----------- + ------ + ------------------ + ----------- ------- + -------------------- +

Basically, I need to add COLUNM to each ROW by doing a certain calculation to display the result:
- COLUMN "End Value" showing Remaining Value of Product to which they have not been delivered:
Qtd - QtD Distain * ValueUnit = End Value

And how to sum all these values after the display? I do not think SUM(ValorPendente) will work for this case.

    
asked by anonymous 18.07.2017 / 15:06

2 answers

3

If you want this in SELECT , bas make a query like this:

SELECT Produto, Qtde, QtdeEntregue, ValorUnitario, ((Qtd - QtdEntregue) * ValorUnitário) ValorPendente
FROM sua_tabela

But if you want to create a field in your table to store it via MySQL , you need to create a TRIGGER .

    
18.07.2017 / 15:11
1

Just do what you already did in the question, that is.

select (Qtd - QtdEntregue) * ValorUnitário as 'ValorPendente'
from MyTabela

and if the total value just suffices.

select sum((Qtd - QtdEntregue) * ValorUnitário) as 'ValorPendenteTotal'
from MyTabela
    
18.07.2017 / 15:17