Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: Sum of fileds in every row

    Hello guys, I have a table with 10 columns.
    One of them is empty.
    I want to calculate sum of the 5 of them, in each row, and put it in the empty column.
    Also I want to find max, min and average in every row.
    I suppose that if i do the first, the other are easy.

    Thanks
    Last edited by fazer; 08-20-09 at 04:54.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    CREATE TABLE x (
       id   char(1) PRIMARY KEY
     , col1 decimal(5,2)
     , col2 decimal(5,2)
     , col3 decimal(5,2)
     , col4 decimal(5,2)
     , col5 decimal(5,2)
    )
    
    INSERT INTO x (id, col1, col2, col3, col4, col5) VALUES ('A', 1, 2, 3, 4, 5)
    INSERT INTO x (id, col1, col2, col3, col4, col5) VALUES ('B', 5, 5, 5, 5, 5)
    INSERT INTO x (id, col1, col2, col3, col4, col5) VALUES ('C', 0, 8, 5, 7, 9)
    
    -- We need to effectively UNPIVOT the columns to be able to get meaningful aggregates
    SELECT id
         , Sum(value) As sum_for_row
         , Avg(value) As avg_for_row
         , Max(value) As max_for_row
         , Min(value) As min_for_row
    FROM   (
            -- UNION each column along with the PK
            SELECT id
                 , col1 As value
            FROM   x
            UNION ALL
            SELECT id
                 , col2
            FROM   x
            UNION ALL
            SELECT id
                 , col3
            FROM   x
            UNION ALL
            SELECT id
                 , col4
            FROM   x
            UNION ALL
            SELECT id
                 , col5
            FROM   x
           ) As x
    GROUP
        BY id
    
    -- Tidy up
    GO
    DROP TABLE x
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you forgot to put the sum into the empty column, george

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2009
    Posts
    2
    gvee it works grate
    thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •