Sum between Mysql lines

Question

I’m having trouble performing an operation using Mysql, I don’t even know if it is possible to do what I want.

I have a control_de_stock table, in this table I have all the operations that are performed with products.

In controle_de_stock I have a column called tipMovimentacao that is 1 for input and 0 for output; what I want to do is this:

select * sum(quantidade) from controle_de_estoque where tipoMovimentacao = 0 MENOS select * sum(quantidade) from controle_de_estoque where tipoMovimentacao = 0

that is, I want to subtract from each other everything I try from the mistake.

Someone knows what I can do?

Thank you.

Answer 1

With a single query you can get the balance if you condition the sum for each type of movement.

Total inflows - Total outflows

Behold:

select sum(case when tipoMovimentacao = 1 then quantidade else 0 end) 
       - sum(case when tipoMovimentacao = 0 then quantidade else 0 end)
from controle_de_estoque 

Performing a single query to the bank you will have a better performance. The cost is practically half of that performing two select sum().

Answer 2

 select   
 (select * sum(quantidade) from controle_de_estoque where tipoMovimentacao = 0)  
 -  
 (select * sum(quantidade) from controle_de_estoque where tipoMovimentacao = 1) as ESTOQUEATUAL   
from dual 

That’s how it works.

Answer 3

You can use a case, if your intention and subtract the amount between types, there is no way you do for all fields as you used in your script

select * sum(

select * ,
    case whan tipoMovimentacao = 0 then sum(quantidade) end
    -
    case when tipoMovimentacao = 1 then sum(quantidade) end
from controle_de_estoque

or a sub select.

 select   
 (select sum(quantidade) from Products where tipoMovimentacao = 0)  
 -  
 (select sum(quantidade) from controle_de_estoque where tipoMovimentacao = 1)