Calculate value difference from same column after sum with condition

Asked

Viewed 22 times

1

I wanted to calculate the balance of an extract, since I have fields like extrato_tipo and extrato_valor, whereas the extrato_tipo I use to be able to define whether the extract is input or output.

Example

extrato_id | extrato_tipo | extrato_data | extrato_valor

   1       |     e        | 2021-05-09   | 500.00 
   2       |     s        | 2021-05-09   | 400.00
   3       |     e        | 2021-05-09   | 500.00  
   4       |     s        | 2021-05-09   | 400.00

Where: e = input and s = output

If I add all the inputs and decrease by the sum of the outputs, it has to give 200 real. I’m having trouble assembling this query.

mysql

  • You can get the sum of all inputs and the sum of all outputs?

  • I managed to solve

  • pq I had mounted another query, before Ricardo reply, mark his as solution!

1 answer

3


You can do it using one CASE WHEN within the SUM, returning "value statement" when "type statement" is equal to 'e' and "value statement * -1" otherwise, which will add and subtract:

SELECT SUM(CASE WHEN extrato_tipo = 'e' THEN extrato_valor 
           ELSE (extrato_valor * -1) END) 
 FROM  nome-da-tabela;

You can see the query working here: http://sqlfiddle.com/#! 9/174b15/3

  • i put the way I found as a solution, speaking of performance, which way is better? I believe yours, the query is cleaner

  • i have not seen your solution, but if the table has little data should be no problem any query, now if the table has a lot of data, the best alternative would be to add the inputs and outputs in two separate queries, and maybe you need an index in "extracto_type"because it needs a Where in this field (or the case), but all this only with data even to be sure :)

  • I had used subquery of the original form

Browser other questions tagged mysql

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