> From: "Pavel Cisar"
> > I have problems with NULL fields.
> >
> > SELECT Code, Sum(Amount) from TableA
> >
> > the problem if the AMOUNT is NULL then SUM field is NULL. But I need the Value ZERO (0)
> >
> > I tried to format it
> >
> > SELECT Code, SUM(CASE Amount WHEN NULL THEN 0 else Amount) from TableA
>
> Well, the proper test for null is with IS or IS NOT NULL, so it should
> look like
>
> SELECT Code, SUM(CASE Amount WHEN IS NULL THEN 0 else Amount) from TableA
>
> But you should rather use COALESCE in this case :-)
>
> COALESCE allows a column value to be calculated by a number of
> expressions, from which the first expression to return a non-NULL value
> is returned as the output value.
>
> In your example it would be:
>
> SELECT Code, SUM(COALESCE(Amount,0)) from TableA

No. Aggregate functions (like SUM) automatically skip NULL values,
so you do not need any additional constructs inside SUM(...).
SUM() is null if there are no rows to sum, so the COALESCE must be outside
(like in Arno's examples).

Ivan