Aggregation replacement

I have to aggregate (sum) a metric and round the result to 2 decimals. If the result of rounding is 0 the expectation is not to show anything, I mean result should be null.

eg:

round(sum(report.QTY),2)

some rows of report.QTY are very low values such as 0.0005 etc, and the rounding results to 0. How will I show the result as null.

I can think of the following,

case when round(sum(dl.QTY),2)=0 then null else round(sum(dl.QTY),2) end

wh开发者_StackOverflowich I think may not be the best way or the only way. Appreciate any help.

SQL Server supports NULLIF:

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

So you could use:

NULLIF(round(sum(dl.QTY),2),0)

I believe that this is supported in most other products also.

For future reference, although SQL is meant to be a standard language, there are sufficient differences between products that it's worth tagging your question with the actual system being used.

yes, konerak is right, but if you still want to use CASE then intstead of what you have done... you can try out like this way... maybe

case when dl.QTY<0.01 then 0 else round(sum(dl.QTY),2) end

please keep 0 instead of null values... will be good for fetching results..