Calculating a weighted average?

Jay Cyrus 0
Viewed 46

ProductData

Respondant ProductNo
1 Chips
2 Fish
3 Chips
4 Fish

ConsumerData

Respondant Demographics Weight
1 87 34
2 16 432
3 34 54
4 56 212

ScoreData

Respondant Date Score
1 2022-03-03 4
2 2022-03-03 8
3 2022-03-03 3
4 2022-03-03 2

Input data available involves ratings given by respondents to products. The three input tables each store information related to the product, the respondent and the score given by respondents to products in specific days. The "Respondent" field of each table is used to match the three tables.

What I need is computing an average rating for each product, weighted by each respondent weight.

I can't seem to get the weight to work. So far I've got this.

SELECT SUM(ScoreData.score * ConsumerData.weight) / SUM(ConsumerData.weight) AS AverageRating
FROM      ScoreData  
LEFT JOIN ConsumerData     
       ON ScoreData.respondantID = ConsumerData.respondantID     
WHERE date = '2022-04-05'

This seems to be returning nothing even though both tables have integers in them to get the weight. Its not returning null, there is just nothing being placed into the table.

Any help is appreciated, thanks.

mysql sql
1 Answers

As already suggested in the comments by @Jan, the empty output you get may be related to the filtering operation you carry out on the date (WHERE date = '2022-04-05'), which would result in no rows in case no respondent has rated any product in that specific day.

There are a couple of issues in your query:

  • you don't have references to the products table, you need to add one further join operation to have full information
  • when you apply aggregate functions like SUM and you want to partition the aggregation on different products, you need to add a GROUP BY clause, which contains the partitioning field
SELECT p.ProductNo,
       SUM(c.Weight * s.Score) / SUM(c.weight) 
FROM       ProductData  p
INNER JOIN ConsumerData c
        ON p.Respondant = c.Respondant
INNER JOIN ScoreData    s
        ON p.Respondant = s.Respondant
GROUP BY p.ProductNo

Check the demo here.

userQZpQ0
Related