sum columns into column in same table -- sql server (alternate)

Roy Harvey
6/26/2007 4:48:00 PM

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

[quoted text, click to view]

azriley NO[at]SPAM gmail.com
6/26/2007 8:33:07 PM

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.

M A Srinivas
6/26/2007 9:24:31 PM

[quoted text, click to view]

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

Ed Murphy
6/26/2007 9:43:48 PM

[quoted text, click to view]

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.

[quoted text, click to view]

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

azriley NO[at]SPAM gmail.com
6/27/2007 5:09:41 PM

[quoted text, click to view]

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.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : sum columns into column in same table