Add and subtract value from the same SQL column

Navigation
3

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 this case the result is 2 .

My query looks 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 do not want to limit the search to only the 11430001 article, but rather all.

However, when I remove 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 =,! =, & Lt ;, < =, > & gt ;, > = or when the subquery is used as an expression.

    
asked by anonymous 12.01.2016 / 19:20

4 answers

0

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
    
12.01.2016 / 22:38
0

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
    
12.01.2016 / 20:42
-1
  

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

  • In the sub-select to get the total amount of the warehouse 4, I added a SUM , this way you will only get the total value, and you can perform the subtraction.
  • Where you did a check for other stores, replace with the command IN
  • I've added an ID to the table using AS , so it's 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
    
12.01.2016 / 19:45
-1

For your SQL Query to return the correct values without limitation, you need to match 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 for the two tables, so you can not mix the fields!

    
13.01.2016 / 01:29
Disable page drag on a mobile device Is there any problem in omitting the semicolon in a php tag with an expression only