CODEWITHSUNDEEP
sql-server
ConnorU
ConnorU

Reputation: 1409

Error converting varchar to numeric in SQL subtract query

I have this query

select SUM(quantity) from Sales join Document on Sales.IdDocument = Document.IdDocument where (IdTipoDocumento = 'vco' or IdTipoDocumento = 'vcr') and IdArticulo = '01-00321' and IdListaEmpresa = 1000129
-
(select SUM(quantity) from Sales join Document on Sales.IdDocument = Document.IdDocument where IdTipoDocumento = 'dco'  and IdArticulo = '01-00321' and IdListaEmpresa = 1000129)

If I run each line separately, each query works fine (the first gives 419 as a result, the second 380) but if I try to run the whole thing to get the result (419-380) it tells me "Error converting data type varchar to numeric."

They're both supposed to already be numeric (the quantity field is of integer type anyways) so I don't know why the error comes out...

Upvotes: 0

Views: 383

Answers (1)

Anup Agrawal
Anup Agrawal

Reputation: 6669

Method 1: 

SELECT
(select SUM(quantity) from Sales join Document on Sales.IdDocument = Document.IdDocument 
where (IdTipoDocumento = 'vco' or IdTipoDocumento = 'vcr') and IdArticulo = '01-00321' and 
IdListaEmpresa = 1000129)

-
(select SUM(quantity) from Sales join Document on Sales.IdDocument = Document.IdDocument 
where IdTipoDocumento = 'dco'  and IdArticulo = '01-00321' and IdListaEmpresa = 1000129)

Method 2:

SELECT SUM(SQ)
FROM (
SELECT CASE WHEN (IdTipoDocumento = 'vco' or IdTipoDocumento = 'vcr') THEN SUM(Quantity)
WHEN IdTipoDocumento = 'dco' THEN -1*SUM(Quantity)
END AS SQ
FROM  Sales join Document on Sales.IdDocument = Document.IdDocument 
where (IdTipoDocumento IN ('vco', 'vcr','dco')) and IdArticulo = '01-00321' and 
IdListaEmpresa = '1000129'
GROUP BY IdTipoDocumento) AS T1

Method 3:

SELECT SUM(CASE WHEN IdTipoDocumento IN ('vco', 'vcr') THEN Quantity ELSE -1*Quantity END)
AS SQ
FROM  Sales join Document on Sales.IdDocument = Document.IdDocument 
where (IdTipoDocumento IN ('vco', 'vcr','dco')) and IdArticulo = '01-00321' and 
IdListaEmpresa = 1000129

Method 3a:

   SELECT IdListaEmpresa, 
         SUM(CASE WHEN IdTipoDocumento IN ('vco', 'vcr') 
                  THEN Quantity 
                  ELSE -1*Quantity END) AS SQ
   FROM  Sales join Document on Sales.IdDocument = Document.IdDocument 
   WHERE (IdTipoDocumento IN ('vco', 'vcr','dco')) 
       AND IdArticulo = '01-00321' 
       AND IdListaEmpresa IN (1000129, 1000130, 1,2,3,4,5)
   GROUP BY IdListaEmpresa

Upvotes: 1

Related Questions