Select sum based on value of other column

Question

I have a table with values like

ID | CODE | QUANTITY
====================
1  | 2    | 20
2  | 2    | 40
3  | 5    | 10
4  | 6    | 15
5  | 5    | 20
6  | 6    | 50
7  | 6    | 10
8  | 7    | 20
9  | 8    | 100

I have a requirement to get the sum of all quantities with "CODE" = 2. However, if the sum is 0 then return the sum of all quantities where "CODE" in (5,6). The idea is to ignore all other codes except 2, 5, and 6, with 2 as the first preference for sum.

I have tried this

WITH CTE AS(
SELECT
    SUM(CASE WHEN CODE = '2' THEN QUANTITY ELSE 0 END) AS QUANTITY1,
    SUM(CASE WHEN CODE IN ('5', '6') THEN QUANTITY ELSE 0 END) AS QUANTITY2
FROM TABLE1
)
SELECT CASE
            WHEN QUANTITY1 <> 0 THEN QUANTITY1
            ELSE QUANTITY2
        END
FROM CTE

It does work but I feel it can be improved and can be done in minimum steps. How can I improve it?

Edit1: The value of QUANTITY column can be 0 in TABLE1 Edit2: sqlfiddle

Answer 1

For the sum of quantities with CODE = '2' use ELSE 0 in the CASE expression and NULLIF(), so that the result is NULL even if the sum is 0:

SELECT COALESCE(
         NULLIF(SUM(CASE WHEN CODE = '2' THEN QUANTITY ELSE 0 END), 0),
         SUM(CASE WHEN CODE IN ('5', '6') THEN QUANTITY END)
       )
FROM TABLE1

You can use ELSE for quantities with CODE IN ('5', '6') too:

SELECT COALESCE(
         NULLIF(SUM(CASE WHEN CODE = '2' THEN QUANTITY ELSE 0 END), 0),
         SUM(CASE WHEN CODE IN ('5', '6') THEN QUANTITY ELSE 0 END)
       )
FROM TABLE1

See the demo.