SQL Weighted averages of multiple rows –

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;

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.

Advertisement

Answer