Re: sum columns into column in same table

On Jun 27, 12:43 am, Ed Murphy <emurph...@xxxxxxxxxxxx> wrote:
azri...@xxxxxxxxx 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.

.

Relevant Pages

  • Re: ALU Implementation
    ... assign sum = a + b; ... when you execute that line of code, Verilog sets up a ... opinion you should NEVER use them in synthesisable design. ... DOULOS - Developing Design Know-how ...
    (comp.lang.verilog)
  • wrong time delay in 1-bit arithmetic unit
    ... I'm trying to build a 16-bit arithmetic unit by combining sixteen 1- ... Here's the link to the overall explanation of the design I'm ... there should be 6ns delay to both the sum ... module fullAdder(a, b, cin, sum, cout); ...
    (comp.lang.verilog)
  • Re: Sum totals not showing up in Queries or Reports
    ... I went to the properties of that "total" field and made the Control ... Source to be the sum of the corresponding 6 fields I want summed. ... Storing calculated data goes against the rules of database design. ...
    (microsoft.public.access.gettingstarted)
  • RE: New Database
    ... Is this in a new design view? ... Is this while I'm creating the view from an already established database. ... and I need to know what to put in here to pull the information that I ... If, in design mode, I try to sum, but I want to sum by pulling from ...
    (microsoft.public.access.gettingstarted)
  • Re: Stored Procedures - Temporary Tables
    ... Pro SQL Server 2000 Database Design ... timestamp, edition_name) ... AS Expr1, ... > --compute sum by ...
    (microsoft.public.sqlserver.programming)