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.

Gordon Linoff · Accepted Answer · 2013-03-05 20:35:29Z

2

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

edited Mar 5, 2013 at 20:35

answered Mar 1, 2013 at 16:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mo2 Over a year ago

The SELECT statement works, however, I need this to work with an UPDATE statement. I know you have to use a CTE, but I'm having a bit of trouble.

Mo2 Over a year ago

Appreciate the help. One last problem. I have another column in there called LoadDt which basically reflects the date that row was inserted. I want it to update the actual table only where: LoadDt = (SELECT MAX(LoadDt) From [TableName]) When I add that where clause either in after the FROM statement or after the SET statement, it tells me 203 rows were affected, however all the rows with the latest LoadDt are still unaffected.

Mo2 Over a year ago

NVM, Figured it out. You forgot to SELECT the MoneyAmountAvg column in your SELECT statement that was inside your UPDATE statement and I accidentally updated the MoneyAmount column instead without noticing.

Mo2 Over a year ago

One last question. Is there anyway to tell it to set the newval expression as a decimal? I'm getting some numbers rounded down when they should be decimals.

marc_s · Accepted Answer · 2013-03-01 16:35:43Z

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:

enter image description here

answered Mar 1, 2013 at 16:35

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Comments

GarethD · Accepted Answer · 2013-03-01 16:35:39Z

0

You can do this using analytic functions:

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

Example on SQL Fiddle

answered Mar 1, 2013 at 16:35

GarethD

70k11 gold badges90 silver badges130 bronze badges

2 Comments

Gordon Linoff Over a year ago

The question isn't the overall average, just the value in the column divided by the number of times.

GarethD Over a year ago

@GordonLinoff Thanks, was making the change as you commented having seen the SQL Fiddle gave the wrong result.

Benjamin M · Accepted Answer · 2013-03-01 16:35:47Z

0

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
...

answered Mar 1, 2013 at 16:35

Benjamin M

24.7k34 gold badges135 silver badges211 bronze badges

Comments

Marlin Pierce · Accepted Answer · 2013-03-01 16:37:48Z

0

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

answered Mar 1, 2013 at 16:37

Marlin Pierce

10.1k5 gold badges33 silver badges56 bronze badges

Comments

mdn · Accepted Answer · 2013-03-01 16:39:18Z

0

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.

answered Mar 1, 2013 at 16:39

mdn

2521 silver badge6 bronze badges

Collectives™ on Stack Overflow

SQL Server COUNT query with unique values

6 Answers 6

4 Comments

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related