Home / Questions / 8982
Accepted
Juan Manuel Palacios
Asked: 2020-05-03 15:50:06 +0800 CST 2020-05-03 15:50:06 +0800 CST

Why can't I SUM a CASE statement?

  • 772

I am creating a new query ( query ) to obtain the sum of the amounts of entry and exit of a warehouse. The first thing I have done is formulate a query that allows me to separate the inputs from the outputs by placing a sign -in front of the amount when it is input, I have done that with a case when:

select productoid, descripcion, codigodelproveedor, marcacodigo, modelo, codigopropio, 
       case tipooperacion when 'S' then cantidad when 'I' then cantidad * -1 end as cantidad 
from ViewParteDeAlmacenProductosDescriptivo

Now what I need is to do a sumwith the result of casebut I am getting the following error:

Msg 195, Level 15, State 10, Line 2 'sum' is not a recognized built-in function name.

This is the new query:

select productoid, descripcion, codigodelproveedor, marcacodigo, modelo, codigopropio, 
       sum(case tipooperacion when 'S' then cantidad when 'I' then cantidad * -1 end as cantidad) as cantidad 
from ViewParteDeAlmacenProductosDescriptivo 
group by productoid, descripcion, codigodelproveedor, marcacodigo, modelo, codigopropio

Any ideas?

sql

2 Answers

  1. Best Answer

    Try removing the as quantity inside the sum, I've also changed the name of the external quantity variable so that it doesn't match the one you use inside, just in case:

    
select productoid, descripcion, codigodelproveedor, marcacodigo, modelo, codigopropio, 
       sum(case tipooperacion when 'S' then cantidad when 'I' then cantidad * -1 end ) as cantidad1 
from ViewParteDeAlmacenProductosDescriptivo 
group by productoid, descripcion, codigodelproveedor, marcacodigo, modelo, codigopropio
    • 3

  2. I think the issue is to place the SUM function inside each case, the as would have no problem:

    SELECT productoid, descripcion, codigodelproveedor, 
       marcacodigo, modelo, codigopropio, 
       CASE tipooperacion 
            WHEN 'S' THEN
                SUM(cantidad) 
            WHEN 'I' THEN 
                SUM(cantidad * -1) 
       AS cantidad 
       FROM ViewParteDeAlmacenProductosDescriptivo 
       GROUP BY productoid, descripcion, codigodelproveedor, 
                marcacodigo, modelo, codigopropio

    In this way it will calculate the sum of the quantities according to the case .

    • 1