Solved

SQL: AVG of an alias

Posted on 2014-10-02
2
162 Views
Last Modified: 2014-10-13
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
Comment
Question by:itmtsn
2 Comments
 
LVL 20

Assisted Solution

by:dsacker
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)

Open in new window

Of course, you'll have to then group all of your other selected fields in a GROUP BY.
0
 
LVL 69

Accepted Solution

by:
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
            FROM [DJS-iSAMS-DJS].[dbo].[VwReportsManagementAcademicReports]
...
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

829 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