Calculating the Variance (i think)

[chazup]

I have a bunch Facilities I go to, and Some data about each one. I wanted to know how I would be able to calculate how much above the average a facility is.
ex: Here's my Query Result right now:
Facility Average P/H
A-----------3.5
B ----------3.67
C---------- 3
D-----------4.1

I want to be able to add a 3rd column that tells me how much above or below the average each facility is. Does anyone know how this is done? I think it's called Variance but I can't get it to work.

 

[ByteMyzer]

Consider a scenario like the following:

Table: tblFacility
------------------
Facility
P/H

The following SQL syntax should show you Average and Variance by Facility from this structure:

SELECT T1.Facility, Avg(T1.[P/H]) AS [Average P/H],
       Avg(T1.[P/H]) - (SELECT Avg(T2.[P/H]) FROM tblFacility AS T2)
       AS [Variance P/H]
FROM tblFacility AS T1
GROUP BY T1.Facility;

 

[Ron_dK]

Agree with Bytemyzer there, but would never name a field P/H. Leave out the / and rename the field to e.g. pHvalue or something.

 

[chazup]

It's working Thanks a ton. Would you mind explaining to me exactly whats going on in the code though?

And Ron, What is the problem with having a "/" in your field names

 

[Ron_dK]

You might run into troubles when fieldnames with certain special characters are used in a query .

See also :
http://support.microsoft.com/kb/826763