Create COLUNM with results from an account made within a ROW - Mysql

Asked

Viewed 20 times

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 up the values of products that have not yet been delivered, ex:

My Records:
+-----------+------+------------------+------------------+
| Product | Qty. | Qtdentregue | Unit value |
+-----------+------+------------------+------------------+
|. Box ..|.. 5 .. |......... 2......| .... 30,00.......|
+-----------+------+------------------+------------------+
|... Ball...|.. 8 .. |.......... 4......| .... 50,00.......|
+-----------+------+------------------+------------------+

I’d like you to exhibit:
+-----------+------+------------------+------------------+--------------------+
| Product | Qty. | Qtdentregue | Unit Value | Pending Value |
+-----------+------+------------------+------------------+--------------------+
|. Box ..|.. 5 .. |............ 2......| .... 30,00....|..... 90,00.........|
+-----------+------+------------------+------------------+--------------------+
|... Ball...|.. 8 .. |............ 4......| ..... 50,00....| .... 200,00.......|
+-----------+------+------------------+------------------+--------------------+

Basically, I need to add a COLUNM in each ROW making a certain calculation to display the result:
- COLUMN "Value Pending" displaying Remaining Value of Product not delivered:
Qty - Qtdentregue * Unit Value = Pending Value

And how to add up all these values after the display ? I do not believe that the SUM(ValorPendente) It will serve this case.

mysql

  • Would that be a select ? or you want to create a column even in your table?

  • Only one view, one Select, no need to store.

2 answers

3


If you want it in one 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 this via MySQL, it will be necessary to create a TRIGGER.

  • It worked perfect, no need to store, just display even.

  • Is there a way to add these values ? Maybe through an Executescalar ?

  • 1

    There is, put one SUM before, in this way: SELECT Produto, Qtde, QtdeEntregue, ValorUnitario, SUM(((Qtd - QtdEntregue) * ValorUnitário)) ValorPendente
FROM sua_tabela, doesn’t need the ExecuteScalar.

1

Just do what you have already asked in the question, ie.

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

and if the total value just add.

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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.