Solved

# SQL: AVG of an alias

Posted on 2014-10-02
170 Views
Hi,

We want to compute the average of the djsresult field and save that as a new column.

we're using:

,Case WHEN (txtResultName IS NULL OR txtResultName = 'Average Point Score') THEN intGradeTransposeValue ELSE txtresult END as djsresult

How do I then calculate an Average of the djsresult field and include it in the results table as a column.

Full code attached
icode.txt
0
Question by:itmtsn
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 20

Assisted Solution

dsacker earned 250 total points
ID: 40357462
You have to wrap it with the AVG function:
``````AVG(Case WHEN (txtResultName IS NULL OR txtResultName = 'Average Point Score') THEN intGradeTransposeValue ELSE txtresult END as djsresult)
``````
Of course, you'll have to then group all of your other selected fields in a GROUP BY.
0

LVL 69

Accepted Solution

Scott Pletcher earned 250 total points
ID: 40357489
You'll need to decide what you want to AVG over/by, and those columns in the PARTITION BY:
...
LEFT JOIN
(SELECT * from
(SELECT [TblReportsStoreID]
,[txtSchoolID]
--...
,[txtPosition]
,Case WHEN (txtResultName IS NULL OR txtResultName = 'Average Point Score') THEN txtGradingName ELSE txtResultName END as djsresulttype
,Case WHEN (txtResultName IS NULL OR txtResultName = 'Average Point Score') THEN intGradeTransposeValue ELSE txtresult END as djsresult
,AVG(Case WHEN (txtResultName IS NULL OR txtResultName = 'Average Point Score') THEN intGradeTransposeValue ELSE txtresult END)
OVER (PARTITION BY <your_column_names_go_here>) AS Avg_djsresult
,DENSE_RANK() OVER (ORDER BY dtReportCyclePrintDate desc, intReportCycleID desc) AS intCycleRank
...
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month4 days, 18 hours left to enroll