# Thread: Sum of fileds in every row

1. Registered User
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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```

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

4. Registered User
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
•