Example 1
Average product scores by response count
The Basic product does not average to 90 because the score of 100 came from three times as many responses as the score of 80. Its weighted score is (80 10 + 100 30) / 40 = 95.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE rating_batches (product VARCHAR(20), score INT, responses INT);
INSERT INTO
rating_batches (product, score, responses)
VALUES
('Basic', 80, 10),
('Basic', 100, 30),
('Pro', 70, 20),
('Pro', 90, 20);Validated query Shared across supported engines.
SQL
SELECT
product,
SUM(score * responses) / SUM(responses) AS weighted_score
FROM
rating_batches
GROUP BY
product
ORDER BY
product;Expected result Returned rows for the shared example.
| product | weighted_score |
|---|---|
| Basic | 95 |
| Pro | 80 |
The examples use values whose weighted averages are whole numbers, avoiding engine-specific decimal display differences.