Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rows

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-30 : 02:09:46

On my table, I have 3 columns and 3 rows.

How can I get the average of each row of the 3 columns?

This means, I'll be getting 3 results.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-30 : 02:56:39

SELECT (col1 + col2 + col3)/3 * 100.0 FROM YourTable

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-30 : 02:58:24

But I like the average of each row. How can I do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-30 : 03:01:47

Didnt get what you're asking for? What i posted will give you average of three column values for each row. Can you explain with some sample data what exactly you're looking at?

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-30 : 03:05:20

Just want to get the average of each row (3 rows). This means I should have 3 results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-30 : 03:10:15

average of each row? isnt that same as average of all the 3 columns in each row? Can you post sample data to make it clear?

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-30 : 03:18:45

Yes average of each row.

Example

Apple 3 4 5
Orange 4 5 8
Grapes 4 6 7

I want to get the average for each row (Apple, Orange and Grapes)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-30 : 03:24:24

SELECT Item,(val1+val2+val3)* 1.0/3 FROM Table

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-30 : 03:28:15

What should I put on "Item" and with val1+val2+val3?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-30 : 03:29:52

Item is your column name containing Apples,Orange,.... & val1,val2,val3 are your value columns.

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-30 : 03:38:10

what value columns? kinda confuse on that part? can you do the formula for me? thanks

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-30 : 04:25:49

I think I got it. However, I want the results on 3 different columns. Is there a way? thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-30 : 06:12:33

SELECT 100.0 * SUM(CASE WHEN Item = 'Apple' THEN Val1 + Val2 + Val3 ELSE 0 END) / 3.0 AS Apple,
100.0 * SUM(CASE WHEN Item = 'Orange' THEN Val1 + Val2 + Val3 ELSE 0 END) / 3.0 AS Orange,
100.0 * SUM(CASE WHEN Item = 'Grape' THEN Val1 + Val2 + Val3 ELSE 0 END) / 3.0 AS Grape
FROM YourTable

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-30 : 06:31:59

thanks peso. it works :)

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-30 : 22:34:41

I'm getting Ambiguous column name error message. Any idea? thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-31 : 04:51:45