SQL Server containing case

Question

I made this query:

SELECT 
  PRODUCTO,
  CASE
    WHEN TIPOMOV=02 THEN SUM((CANT*-1)/6)
    WHEN TIPOMOV=10 THEN SUM((CANT*-1)/6)
    WHEN TIPOMOV=06 THEN SUM(CANT/6)
  END AS CANTIDAD
FROM MOVPROD
GROUP BY PRODUCTO

but I am getting this error:

Column 'MOVPROD.TIPOMOV' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 8120, Level 16, State 1, Line 5

please help!

Answer 1

This should work-

SELECT 
PRODUCTO, 
SUM(
    CASE 
        WHEN TIPOMOV = 02 THEN ISNULL((CANT*-1),0)/6) 
        WHEN TIPOMOV = 10 THEN ISNULL((CANT*-1),0)/6) 
        WHEN TIPOMOV = 06 THEN ISNULL((CANT/6),0) 
    END
) AS CANTIDAD 
FROM MOVPROD 
GROUP BY PRODUCTO

Answer 2

The CASE expression should be the argument to the SUM():

SELECT PRODUCTO, 
       SUM(CASE WHEN TIPOMOV = '02' THEN (CANT*-1)/6) 
                WHEN TIPOMOV = '10' THEN (CANT*-1)/6) 
                WHEN TIPOMOV = '06' THEN (CANT/6) 
           END) AS CANTIDAD 
FROM MOVPROD 
GROUP BY PRODUCTO;