[share] SQL query- sum total with null value.

Besides using Linq2Entites. I also have to finger out that SQL query while doing Crystal report for display report.

SELECT ([size1] + [size2] + [size3]) as sumOfTotal
FROM [TableQty];

Where the [size2] is a null value and I have meet a problem that if I total up all the qty with a null value, the answer will be "NULL".

LoL, how can I just show the "NULL" in report?


So that, searching the solution from online again. And I find out that has a pretty good solution.


Here is the code:

SELECT (ISNULL([size1],0) + ISNULL([size2],0) + ISNULL([size3],0)) as sumOfTotal
FROM [TableQty];

Then the "NULL" value with be exchange to "0".


Here is the other option but only for "SELECT" not for "SUM".

SELECT (CASE WHEN ([size1] IS NULL) THEN 0 ELSE [size1] END AS [size1]) 
FROM [TableQty];

Hope it can help you and have a nice day.