SQL Server COUNT query with unique values

Question

This is done in Microsoft SQL Server 2008 R2.

I'll start out with an example table.

Organization | MoneyAmount | MoneyAmountAvg

ISD          | 500         | 
ISD          | 500         | 
ISD          | 500         | 
QWE          | 250         | 
ISD          | 500         | 
QWE          | 250         | 
OLP          | 800         | 
ISD          | 500         | 

I need the MoneyAmountAvg column to have a value of MoneyAmount/(# of times that organization shows up

So for example, the MoneyAmountAvg column for the ISD rows would have a value of 100.

QWE would have 125 for each row in the MoneyAmountAvg column and OLP would have a value of 800 since it is there only once.

This is only an example table. The actual table is much bigger and has more organizations, but it has the same criteria. Some organizations have multiple rows, while others are there only once.

I just need a way for it to count how many times each organization is listed when I use an update statement for that organization's MoneyAmountAvg column. Hard coding it for each organization is definitely not an option since they can change at any moment.

Any help is appreciated.

Answer 1

Here is my answer:

select organization, moneyamount,
       moneyamount / count(*) over (partition by organization)
from t

This is a simple application of a window function. I think most of the other answers are producing the overall average.

For an update statement, simply do:

 with toupdate as (
    select organization, moneyamount,
           moneyamount / count(*) over (partition by organization) as newval
    from t
 )
 update toupdate
       set MoneyAmountAvg = newval

Answer 2

Try something like this:

;WITH CTE AS
(
    SELECT
        Org, Moneyamount,
        MoneyAvg = AVG(MoneyAmount) OVER(PARTITION BY Org),
        OrgCount = COUNT(*) OVER (PARTITION BY Org)
    FROM 
        dbo.YourTableHere
)
SELECT DISTINCT Org, MoneyAmount, OrgCount, MoneyAvg / OrgCount
FROM CTE

That seems to return what you're looking for: