Add and Subtract value from the same SQL column

Question:

I have a table with the following data:

+---------------------------+
| Cod_Art | Armazem | Quant |
+---------------------------+
| 11430001 |   1    |    0  |
| 11430001 |   2    |    3  |
| 11430001 |   3    |    0  |
| 11430001 |   4    |    1  |
+---------------------------+

What I need is to run a query with the sum of the amounts of:

Armazem 1 + Armazem 2 + Armazem 3 - Armazem 4

In this case the result is 2 .

My query like this:

select cod_Art, (sum(quant) - (select quant from ccartigos_stock where armazem = 4 and Cod_Art = '11430001'))  as 'quant' 
from ccartigos_stock 
where (armazem = 1 or Armazem = 2 or Armazem = 3) and Cod_Art = '11430001'
group by cod_art 
order by cod_Art

However I don't want to limit the search to just the article 11430001 but to all of them.

However, when removing the where Cod_Art = '11430001' I get the following error:

Msg 512, Level 16, State 1, Line 6 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,> >, >= or when the subquery is used as an expression.

Answer:

Try the query below:

select
    cod_Art,
    sum(case
        when armazem = 4 then -quant
        else quant
    end) as 'quant'
from
    ccartigos_stock
group by
    cod_Art
order by
    cod_Art
Scroll to Top