Hi,
I basically do not want to return a null value as a result of using a sum function (using sum against 0 rows).
Is there a common way to avoid this?
Thanx
Interesting you should ask. I was looking at the same problemyesterday. My results are displayed in a DataGrid and thankfully itdoesn't barf on the NULL values. It displays -1 instead. Of coursethat's still not desirable as the correct answer for SUM(nothing)should be 0 IMO.Let's hope someone has a suggestion.
|||You could use the ISNULL function to calculate a 0 whenever the SUM contains a NULL:
SELECT ISNULL(SUM(myColumn,0)) FROM myTable WHERE 0=1
|||
Or, as a slight variation, use ISNULL to replace the result of the SUM for instances where no rows result in an answer of NULL:
SELECT ISNULL(SUM(myColumn),0) FROM myTable WHERE 0 = 1
(Note the different parenthesis placement: tmorton's will replace any NULL value with zero BEFORE aggregation; mine will replace theentire result with 0 AFTER aggregation in the event of a NULL sum).
|||
pjmcb wrote:
Or, as a slight variation, use ISNULL to replace the result of the SUM for instances where no rows result in an answer of NULL:
SELECT ISNULL(SUM(myColumn),0) FROM myTable WHERE 0 = 1
(Note the different parenthesis placement: tmorton's will replace anyNULL value with zero BEFORE aggregation; mine will replace the entireresult with 0 AFTER aggregation in the event of a NULL sum).
Thank you for catching my typo pjmcb! What I actually posted is not even syntactically correct. :-)
I should have copied-and-pasted from Query Analyzer instead of retyping.
|||I didn't think you could do that, but I was too lazy to open up QA to test it out...
