1
I have a table with this structure:
...| tipo | quantidade
E 20
S 15
E 500
the column tipo can be "And", incoming, and "S" exit. and the quantidade is a number.
How can I make a select add all type records "And" and subtracts the sum of all type records "S"?
Your question seems to have some problems and your experience here in Stack Overflow may not be the best because of this. We want you to do well here and get what you want, but for that we need you to do your part. Here are some guidelines that will help you: Stack Overflow Survival Guide in English.
– Maniero
updated title and description
– Thiago
I think you will use two Queries, with SUM and Where, and then in the larger query subtract the result of the first query by the second and you will have the TOTAL. He even tried this?
– Guilherme Nascimento
Yes, I tried that, only the query got a little big, I believe it should have a more "elegant solution"
– Thiago
select sum((case when type = "E' then 1 Else -1 end) * quantity) total from table
– Motta
Just add up all the quantities, but when it is
tipo = 'S'you mutiplica by -1.SUM(IF(tipo = 'E', 1, -1) * quantidade)– Woss
I don’t know if this has a performance issue: https://www.db-fiddle.com/f/cuY4Px9VLQ1hHMjfrHTnp7/4 -- is this what you want? Very rusty :P --- Do @Motta: https://www.db-fiddle.com/f/cuY4Px9VLQ1hHMjfrHTnp7/5
– Guilherme Nascimento
Thank you, I just wanted a detailed reply from @Motta and Woss rs
– Thiago
"CASE" returns 1 or -1 as the rule , making the values positive or negative, then just perform a "SUM" VIDE DOCS http://www.mysqltutorial.org/mysql-case-function/ https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_sum
– Motta