Posted by azriley on 06/27/07 17:09

On Jun 27, 12:43 am, Ed Murphy <emurph...@socal.rr.com> 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.

[Back to original message]