CREATE TABLE `reviews` (
`id` int(11) NOT NULL,
`average` decimal(11,2) NOT NULL,
`house_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `reviews` (`id`, `average`, `house_id`) VALUES
(1, '10.00', 1),
(2, '10.00', 1);
ALTER TABLE `reviews`
ADD PRIMARY KEY (`id`);
ALTER TABLE `reviews`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
CREATE TABLE `dummy_reviews` (
`id` int(11) NOT NULL,
`average` decimal(11,2) NOT NULL,
`house_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `dummy_reviews` (`id`, `average`, `house_id`) VALUES
(0, '2.00', 1);
ALTER TABLE `dummy_reviews`
ADD PRIMARY KEY (`id`);
AND the query
SELECT
AVG(r.average) AS avg1,
AVG(dr.average) AS avg2
FROM
reviews r
LEFT JOIN
dummy_reviews dr ON r.house_id = dr.house_id
the result is
avg1 avg2
10.000000 2.000000
All good by now but (10 + 2) / 2 = 6 ... wrong result
I need (10+10+2) / 3 = 7,33 ... How can I get this result?