SQL Weighted averages of multiple rows -

Question

How would you go about to find the real weighted average of multiple rows:

By real weighted, I mean like this calculator: https://www.rapidtables.com/calc/math/weighted-average-calculator.html (and not by multiplying value with weight).

The weight for each answer is set in answers and values for each question in child-table answer_items. We want the weighted average for each question (a,b,c,d). We know what questions to look for in advance.

The query will include between 2 and 500k answers (so preferably a speedy solution :) )

CREATE TABLE `answers` (
  `id` int(10) NOT NULL,
  `weight` varchar(255) NOT NULL
);

INSERT INTO `answers` (`id`, `weight`) VALUES
(1, '0.7'),
(2, '1'),
(3, '0.7'),
(4, '0.9');

CREATE TABLE `answer_items` (
  `id` int(11) NOT NULL,
  `answer_id` int(11) NOT NULL,
  `question` varchar(5) NOT NULL,
  `value` int(11) NOT NULL
);

INSERT INTO `answer_items` (`id`, `answer_id`, `question`, `value`) VALUES
(1, 1, 'a', 2),
(2, 1, 'b', 4),
(3, 1, 'c', 2),
(4, 1, 'd', 3),
(5, 2, 'a', 4),
(6, 2, 'b', 2),
(7, 2, 'c', 4),
(8, 2, 'd', 1),
(9, 3, 'a', 3),
(10, 3, 'b', 4),
(11, 3, 'c', 1),
(12, 3, 'd', 5),
(13, 4, 'a', 5),
(14, 4, 'b', 2),
(15, 4, 'c', 3),
(16, 4, 'd', 3);


ALTER TABLE `answers`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `answer_items`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `answers`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

ALTER TABLE `answer_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

user_1144035 · Answer

You can multiply the value times the weight and then divide by the sum of the weights. For the weighted average by question:

select question, sum(ai.value * a.weight) / sum(a.weight)
from answer_items ai join
     answers a
     on ai.answer_id = a.id
group by question;

Here is a db<>fiddle.

user_27408 · Answer

Somethin like:

SELECT
   SUM(value * weight) / SUM (weight)
FROM
   answers
JOIN
   answer_items
ON
   answer_items.answer_id = answers.id

(edit: and obviously include the question and GROUP BY question, as in the previous answer for the SUM aggregation, as per the previous answer. D'oh)

SQL Weighted averages of multiple rows -

2 Answers