CODEWITHSUNDEEP
sqljoincountsql-server-2008-r2
Mo2
Mo2

Reputation: 1131

SQL Server COUNT query with unique values

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.

Upvotes: 3

Views: 1566

Answers (6)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

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

Upvotes: 2

mdn
mdn

Reputation: 252

select
    organization
    ,avg(MoneyAmount) / count(1) 
  from tab
  group by organization

The purpouse of avg(Moneyamount) is to handle possible different values of amount for the same organization.

Upvotes: 0

Marlin Pierce
Marlin Pierce

Reputation: 10089

select example.Organization, example.MoneyAmount,
       example.MoneyAmount / count(group_table.Organization) as  MoneyAmountAvg
from example
  inner join example group_table
     on example.Organization = group_table.Organization

Upvotes: 0

Benjamin M
Benjamin M

Reputation: 24527

SELECT (MIN(MoneyAmount) / COUNT(*)) AS AvgMoneyAmount
FROM your_table
GROUP BY Organization

This works if MoneyAmout for a single Organization is always equal.

But I'd say: Refactor the table:

Organization | MoneyAmount | count
----------------------------------
ISD          | 500         | 5
QWE          | 250         | 2
...

Upvotes: 0

marc_s
marc_s

Reputation: 754538

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:

enter image description here

Upvotes: 2

GarethD
GarethD

Reputation: 69769

You can do this using analytic functions:

SELECT  Organization,
        MoneyAmount,
        MoneyAmountAvg = MoneyAmount / COUNT(*) OVER(PARTITION BY Organization)
FROM    T

Example on SQL Fiddle

Upvotes: 0

Related Questions