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..