Totals Column - Blank if Zero or NULL

Question

A report currently computes a "totals" column like so:

,CASE WHEN ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) +
           ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0) = 0                 
      THEN ''
      ELSE CONVERT(VARCHAR(15), 
           ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) +
           ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0)) 
      END AS 'MealsTotal'

How can I get a blank cell when a total equals zero, instead of NULL or "0", and avoid doing the computation twice?

Answer 1

As a suggestion, don't use 'single quotes' for column aliases - it is not supported in certain scenarios, and makes it look like a string literal. If you need to quote column names (in this case you don't), use [square brackets]. Also try to use table aliases to improve readability. If you reference those tables 10 times, we don't really need to see CLM_BREAKFAST_TYPEA 10 times.

To be clear, the above is additional commentary meant to explain why the code sample below doesn't look exactly like yours. The following re-write of your expression does do exactly what your question asks.

,MealsTotal = COALESCE(NULLIF(CONVERT(VARCHAR(15),
       COALESCE(A.MealsA, 0) +
       COALESCE(B.MealsB, 0)),0),'')
FROM
... dbo.CLM_BREAKFAST_TYPEA AS A
... some kind of join
... dbo.CLM_BREAKFAST_TYPEB AS B

SQLfiddle demo