Posted by M A Srinivas on 06/27/07 04:24

On Jun 27, 1:33 am, azri...@gmail.com wrote:
> I have inherited a database that tracks if a customer ordered a
> product, with 1 being a yes and 0 being no. What I want to do is sum
> those columns (customer_tbl.ordered2004, customer_tbl.ordered2005,
> customer_tbl.ordered2006) and set the value of that sum into a column
> in the same table (customer_tbl.customer_rank).
>
> Short of doing a
>
> UPDATE customer_tbl
> SET customer_rank = 3
> WHERE SUM(ordered2004 + ordered2005 + ordered2006) = 3
>
> Is there a better way to update each row's customer_rank based on its
> sum of ordered columns?
>
> Any help would be appreciated.

Be aware that if there are no orders in one of the years and stored
as NULL , your addition (ordered2004 + ordered2005 + ordered2006)
will return NULL .

Better way is to
SUM((ISNULL(ordered2004,0) + ISNULL(ordered2005,0) +
ISNULL(ordered2006,0))

[Back to original message]