CODEWITHSUNDEEP
sqlsql-servert-sql
Charles
Charles

Reputation: 11479

Adding averages to a table

I have a table

| Location | Unit | ...
+----------|------+----
| A        | 1    | ...
| A        | 1    | ...
| B        | 1    | ...
| A        | 2    | ...
| C        | 2    | ...
| ...      | ...  | ...

and I want to compute a new table with the 'average' location value for each unit, in the following sense:

| Location | Unit | Weight |
+----------|------+--------+
| A        | 1    | 0.6667 |
| B        | 1    | 0.3333 |
| A        | 2    | 0.5    |
| C        | 2    | 0.5    |
| ...      | ...  | ...    |

Of course it is simple enough to get the totals

select unit, location, count(*)
from table1
group by unit, location;

and to create the table

create table table2 (Unit nvarchar(50), Location int, Weight float);

but I'm not sure how to populate it with the average data. (This isn't hard, but somehow I'm stuck... it's been many years since I worked on SQL Server.)

Upvotes: 2

Views: 72

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You'd use COUNT OVER for this:

select distinct
  location, unit, 
  cast(count(*) over (partition by unit, location) as decimal) / 
  cast(count(*) over (partition by unit) as decimal) as weight
from mytable
order by unit, location;

SQLFiddle

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want to do a weighted average, with units as the weight. You need both the sum for each location/unit combination and the total for each location. To produce your output:

select location, unit, (1.0*unit) / sum(unit) over (partition by location)
from table1
group by location, unit;

The 1.0* is just a fast way of converting to a decimal value, in the event that unit is actually an integer.

EDIT:

If you just want the counts, then this should work:

select location, unit, count(*) as cnt,
       (1.0*count(*)) / sum(count(*)) over (partition by unit)
from table1
group by location, unit;

Upvotes: 2

franglais
franglais

Reputation: 938

While you haven't said that there is a weight column, I'm assuming there must be (otherwise, what do you mean by weight?)

select unit, location, AVG(weight)
from table1
group by unit, location;

as the select statement

select unit, location, AVG(weight)
into table2
from table1
group by unit, location;

as an insert into a new table

Upvotes: 0

Related Questions