Find Average of an Average? - 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

Find Average of an Average?

Author

Topic

AVIRUS
Starting Member

3 Posts

Posted - 2012-10-10 : 09:40:39

Hello All,

noob here..

I would like to know how do I find the average of an average? I would like to find the average difference of the difference between actualServiceCost and minCharge for each service offered.

I am able to find individual average, but not if the entry has more than one more service code present.

Could anyone help?

This is the code that I have written:

CREATE VIEW ChargeDifferences AS
SELECT R.serviceCode, S.serviceDescription, S.minCharge, R.actualServiceCost , AVG(R.actualServiceCost - S.minCharge) AS averageDifferences

FROM [SERVICE] AS S LEFT JOIN SERVICERENDERED AS R
ON S.serviceCode = R.serviceCode

GROUP BY R.serviceCode , S.serviceDescription , S.minCharge , R.actualServiceCost

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-10 : 09:47:08

You can make your current query into a subquery and then take the average of the averageDifferences. Alternatively, if you are on SQL 2005 or later, you can use the windowing function like this:

AVG(AVG(R.actualServiceCost - S.minCharge)) OVER() AS AvgOfAvg

AVIRUS
Starting Member

3 Posts

Posted - 2012-10-10 : 09:58:30

Thank you for your reply!

I am on SQL Server 2008, i have tried the code and the whole column of AvgOfAvg now has the exact same values through out.

Any idea?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-10 : 10:06:01

I thought that is what you wanted to see. Try one of these - not sure if either of these is it. If it is not, can you post a few rows of sample data along with what you want to see?


SELECT R.serviceCode,
       S.serviceDescription,
       S.minCharge,
       R.actualServiceCost,
       AVG(R.actualServiceCost - S.minCharge) OVER(  PARTITION BY R.serviceCode )AS averageDifferences
FROM   [SERVICE]                  AS S
       LEFT JOIN SERVICERENDERED  AS R
            ON  S.serviceCode = R.serviceCode

or

SELECT R.serviceCode,
       S.serviceDescription,
       S.minCharge,
       R.actualServiceCost,
       AVG(R.actualServiceCost - S.minCharge) OVER()AS averageDifferences
FROM   [SERVICE]                  AS S
       LEFT JOIN SERVICERENDERED  AS R
            ON  S.serviceCode = R.serviceCode

In the brackets after the OVER clause, put whatever columns you want to group by. In the first case, I am grouping by ServiceCode.

AVIRUS
Starting Member

3 Posts

Posted - 2012-10-10 : 10:22:45

It works!!!!

I am really a noob! Thank you so much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources