Solved

SQL Select - AVG

Posted on 2016-09-21
3
55 Views
Last Modified: 2016-09-22
I need to know how to average multiple columns in SQL Select.  There 3 columns in the table and I want to compute two averages.  Average 1 would be average of the Base Model Nbr and Image NBR and Average 2 would be the average of the Base Model Nbr, Image Nbr and New Features Nbr.

My Table:

Supplier ID     Base Model Nbr    Image Nbr  New Features Nbr
100                                   0.04                      0.03                  0.03                    
1002                           0.09                      1.00                  0.00
1006                           0.50                      0.50            1.00
10082                           1.00                      1.00                 1.00

I need two additional columns to compute these averages so the output would look like this:

Supplier ID     Base Model Nbr    Image Nbr  New Features Nbr  Average 1    Average 2
100                                   0.04                      0.03                  0.03                           .035          .0335
1002                           0.09                      1.00                  0.00                          .545           .0363
1006                           0.50                      0.50            1.00                           0.5             0.666
10082                           1.00                      1.00                 1.00                                1             1
0
Comment
Question by:tmajor99
  • 2
3 Comments
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 41809495
If you do not need to compute the average across rows, this simple query should work:
SELECT SupplierID, [Base Model Nbr], [Image Nbr], [New Features Nbr],
    ([Base Model Nbr] + [Image Nbr]) / 2.00 [Average 1], 
    ([Base Model Nbr] + [Image Nbr] + [New Features Nbr]) / 3.00 [Average 2]
FROM <your table>

Open in new window

0
 
LVL 35

Expert Comment

by:YZlat
ID: 41809562
try

SELECT SupplierID, BaseModelNbr, ImageNbr, NewFeaturesNbr, (BaseModelNbr + ImageNbr)/2 As Average1, (BaseModelNbr + ImageNbr + NewFeaturesNbr)/3 As Average2
FROM MyTable

Open in new window

0
 
LVL 35

Expert Comment

by:YZlat
ID: 41809566
Shaune Kline beat me to it:)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question