sum columns into column in same table

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.

Re: sum columns into column in same table

First, SUM acts across multiple rows, but you appear to simply be
adding up three columns. After that and other changes we have

UPDATE customer_tbl
SET customer_rank = ordered2004 + ordered2005 + ordered2006
WHERE ordered2004 + ordered2005 + ordered2006 <> customer_rank
OR customer_rank IS NULL

So it simply assigns the new value IF it is any different than the old
one.

But there may be a better way, which is not to have a customer_rank
column at all. It is easily derived from the other columns as needed.
If you must have it, consider making it a computed column rather than
a physical column. Or it could be added to a view. Either a computed
column or the column in a view can be indexed, see the Books on Line
for restrictions.

Roy Harvey
Beacon Falls, CT

On Tue, 26 Jun 2007 20:33:07 -0000, azriley@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.

Re: sum columns into column in same table

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

Re: sum columns into column in same table

azriley@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)

Eww. Those columns should be ditched, in favor of a separate table
with columns 'customer_id', 'year', 'ordered'. The person who saddled
you with those columns should also be ditched.

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

Under the current design:

update customer_tbl
set customer_rank = ordered2004 + ordered2005 + ordered2006

Under the repaired design:

update customer_tbl
set customer_rank = sum(cy.ordered)
from customer_tbl c
join customer_year_tbl cy on c.customer_id = cy.customer_id
group by c.customer_id

Re: sum columns into column in same table

On Jun 27, 12:43 am, Ed Murphy wrote:
> 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)
>
> Eww. Those columns should be ditched, in favor of a separate table
> with columns 'customer_id', 'year', 'ordered'. The person who saddled
> you with those columns should also be ditched.
>
> > 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?
>
> Under the current design:
>
> update customer_tbl
> set customer_rank = ordered2004 + ordered2005 + ordered2006
>
> Under the repaired design:
>
> update customer_tbl
> set customer_rank = sum(cy.ordered)
> from customer_tbl c
> join customer_year_tbl cy on c.customer_id = cy.customer_id
> group by c.customer_id

thanks all. The update did the trick. I considered using calculated
columns initially or calculating the sum from within asp, but in terms
of performance and maintenance, unfortunately from a database design
perspective, this is as good as it gets.