CODEWITHSUNDEEP
sqlsql-server
zock
zock

Reputation: 223

Weighted averages and total scores by group

I have the following population data:

enter image description here

I'm trying to get the following Total score for each combination of timeperiod-district-region in my dataset:

enter image description here

I've done the calculation in Excel but am struggling to implement it in Microsoft SQL Server:

enter image description here

I've tried implementing everything from joins to window calculations but struggling to get beyond the first step.

This is what I've tried so far:

    select w1.TimePeriod, w1.Region, w1.District, w1.Population/sum(w2.Population)
    from table w1 INNER JOIN table w2
        on w1.TimePeriod=w2.TimePeriod AND w1.Region=w2.Region AND w1.District=w2.District 
    group by w1.TimePeriod, w1.Region, w1.District

Eeeor:

Column 'table.Population' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any help to get me started will be extremely appreciated!

Upvotes: 1

Views: 56

Answers (2)

Nawaf
Nawaf

Reputation: 540

Try this

SELECT *
,sum([Population]) OVER () AS allpopulation
,( cast ( [Population] AS FLOAT) / cast((sum([Population]) OVER () )AS FLOAT)) AS popweigted
,round ( ( cast ( [Population] AS FLOAT) / cast((sum([Population]) OVER () )AS FLOAT)) * [Score] , 2) as "pop_weighted*score" FROM [dbo].[table]

check the result here enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You seem to want:

select timeperiod, district, region,
       sum(population * score) / sum(population)
from t
group by timeperiod, district, region;

You don't need to count the total population for this calculation in SQL.

Upvotes: 1

Related Questions