Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

           ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0) = 0                 
      THEN ''
           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?

share|improve this question
up vote 9 down vote accepted

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.

       COALESCE(A.MealsA, 0) +
       COALESCE(B.MealsB, 0)),0),'')
... some kind of join

SQLfiddle demo

share|improve this answer
Thank you, Aaron; since this seems more like a comment than an answer, as time and motivation permits, would you please delete it and move it to a comment, above, or re-word as an answer? I appreciate the insights, but I'm struggling to see this as an answer to my question. Thank you. – iokevins Aug 8 '13 at 3:11
@schultkl huh? I added commentary in addition to re-writing your MealsTotal calculation. Did you try it before you down-voted it? Did you click on the SQLfiddle link I provided to further demonstrate that the way I wrote the expression does precisely what your question asks? Please try it before down-voting and suggesting that this answer should have been a comment, and maybe ask for clarification before down-voting the only person who has attempted to help you - that's not a great way to show appreciation. – Aaron Bertrand Aug 8 '13 at 3:45
: o ) Thank you Aaron--to answer your questions: No; and no. – iokevins Aug 8 '13 at 17:10
To follow-up: I'll remove the downvote--I do appreciate your help and clarification. When I saw the answer beginning with a discussion of table aliases (late last night), my eyes glazed over and I did not notice you had also supplied an actual answer to the question--I assumed the code only served as an example of your preferences on using table aliases. Looking at the edit history of the answer, I now see how you started with only the answer, then added the (helpful) notes above. Thank you again. – iokevins Aug 8 '13 at 17:17
@schultkl I strongly recommend in the future that you read the entire answer before casting judgment. And remember that you are coming here for help, and people try to help you out of their own free time. Generally we don't care about reputation but punishing us because you didn't read or comprehend is, well, pretty crappy. – Aaron Bertrand Aug 8 '13 at 17:35

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.