calculating problem

• jthill

  SSC Veteran

  Points: 256

  More actions

  April 15, 2006 at 7:15 am

  #169045

  I need to add all columns for each row and produce a result set with the total.

   

  SELECT IntrestMark, TOTAL( col1 + col2 + col3+ col4+ col5+ col6)

  FROM mytable

  GROUP BY IntrestMark

  Is there a way to make this query update the same table, but a column that holds the total?

  I need help asap. Thanks in advance for everyone's help

   

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  April 15, 2006 at 10:53 am

  #632696

  To total the sum of all columns together:

  SELECT IntrestMark, SUM(col1 + col2 + col3 + col4 + col5 + col6) AS [Total]
    FROM mytable
   GROUP BY IntrestMark

  (See "Aggregate Functions" in BOL for more)

  If you want the table to hold the column total value, then you can add a computed column:

  ALTER TABLE mytable ADD Total AS col1 + col2 + col3 + col4 + col5 + col6

  The value won't be physically stored in the table, just the definition of the equation.

  (See "ALTER TABLE statement" in BOL for more.  There some settings that can affect this feature.)

  After altering that table, you could re-write the first query as:

  SELECT IntrestMark, SUM(Total)
    FROM mytable
   GROUP BY IntrestMark

  -Eddie

  Eddie Wuerch
  MCM: SQL

• jthill

  SSC Veteran

  Points: 256

  More actions

  April 15, 2006 at 11:49 am

  #632698

  Thanks I thought I tried that. I did have to add COALESCE to each column since the columns could be NULL. It worked great.

   

  Thanks again and Happy Easter