How can I use CASE expression with SUM function in SQL?

Question

I have an Oracle SQL query that I need to select certain deduction codes and provide a predetermined amount if based on the premium amount for deduction code 91B. These are hard coded values that I need to summarize.

SELECT SUM(DECODE(DEDCD,'91A',AL_AMOUNT,0)) MED91A,
       SUM(CASE WHEN DEDCD = '91B' THEN 
       DECODE(AL_AMOUNT, 23.54,7.85,
                         40.62,8.31,
                          43.85,8.31,
                          56.77,8.31,
                          AL_AMOUNT)) MED91B
    FROM PS_AL_CHK_DED 
    WHERE WEEK_NBR = 6
    AND TO_CHAR(CHECK_DT, 'YYYY')=TO_CHAR(SYSDATE, 'YYYY')

The SUM(DECODE ..) statement used to summarize values where dedcd = '91A' works fine. When I add the part to summarize values where dedcd = '91B' it produces a 'Missing Keyword' error after the decode statement. I'm trying to streamline the query in order to produce the results I need for these two deduction codes because the full query takes entirely too long to run.

Oracle Sql Developer 4.0.2.15

Lalit Kumar B Lalit Kumar B · Accepted Answer · 2015-02-22T06:39:48

You must complete the case expression syntax with an END keyword.

Do it like this:

CASE WHEN DEDCD = '91B' THEN 
       DECODE(AL_AMOUNT, 23.54,7.85,
                         40.62,8.31,
                          43.85,8.31,
                          56.77,8.31,
                          AL_AMOUNT)
END

A basic case expression looks like:

CASE [ expression ]

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

See the documentation for more details.

How can I use CASE expression with SUM function in SQL?

1 Answers