CODEWITHSUNDEEP
mysqlsql
Florin
Florin

Reputation: 6169

Average values from different table on join

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?

SQLFiddle

Upvotes: 3

Views: 735

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Jorge's answer is the simplest approach (and I duly upvoted it). In response to your comment, you can do the following:

select ( (coalesce(r.suma, 0) + coalesce(d.suma, 0)) /
         (coalesce(r.cnt, 0) + coalesce(d.cnt, 0))
       ) as overall_average
from (select sum(average) as suma, count(*) as cnt
      from reviews
     ) r cross join
     (select sum(average) as suma, count(*) as cnt
      from dummy_reviews
     ) d;

Actually, I suggest this not only because of your comment. Under some circumstances, this could be the better performing code.

Upvotes: 3

Jorge Campos
Jorge Campos

Reputation: 23361

You have values joined and as such you wont have 3 rows, you will have 2. What you need is a union so you can have all rows from your average tables and do the calculation from it. Like this:

select avg(average) from
  (select average from reviews
   union all
   select average from dummy_reviews
  ) queries

See it here: http://sqlfiddle.com/#!9/e0b75f/3

Upvotes: 4

Related Questions