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.
Find Average of an Average?
Posted - 10/10/2012 : 09:40:39
