Add and Subtract value from 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 execute a query with the sum of the quantities of:

Armazem 1 + Armazem 2 + Armazem 3 - Armazem 4

In that case the result is 2.

My query this way:

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 only to the article 11430001 but yes to all.

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 1

It’s simpler than you’re trying, using IFF (from SQL Server 2012):

select cod_Art, sum(IIF(Armazem = 4, -quant, quant))
from ccartigos_stock
group by cod_art 
order by cod_Art

Answer 2

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

Answer 3

The error is shown to you because when you remove the filter (Cod_art = '11430001') your query returns more than one record, and not it is possible to subtract the amount. See the changes that made that fix this error and some improvements.

• In sub-select to get the total amount of warehouse 4, I added a SUM, this way you will get only the total value, and you can perform the subtraction.
• Where you did a check for the other warehouses, replace with the command IN
• I added an ID to the table using the AS, so it becomes easier to identify

SELECT 
    CAS.cod_Art, 
    (SUM(CAS.quant) - 
        SELECT 
            SUM(ARM.quant)
        FROM
            ccartigos_stock AS ARM
        WHERE
            ARM.armazem = 4
        group by ARM.cod_art
    ) AS QTDE
FROM 
    ccartigos_stock AS CAS
WHERE 
    CAS.armazem IN (1, 2, 3)
GROUP BY CAS.cod_art 
ORDER BY CAS.cod_Art

Answer 4

For your SQL Query to return the correct values without the limitation, you need to list the 2 Selects, the rest is ok.

SELECT Tabela1.COD_ART
       ,(SUM(Tabela1.QUANT) - (SELECT Tabela2.QUANT
                                 FROM CCARTIGOS_STOCK Tabela2
                                WHERE Tabela2.ARMAZEM = 4
                                  AND Tabela2.Cod_Art = Tabela1.Cod_Art)
                              ) AS 'quant'
  FROM CCARTIGOS_STOCK Tabela1
 WHERE Armazem = 1
    OR Armazem = 2
    OR Armazem = 3
 GROUP BY Tabela1.COD_ART 
 ORDER BY Tabela1.COD_ART   

Note that I gave nicknames to the two tables, so you can not mix the fields!