MySQL return distinct accounts from one table, average rating from another, all based on service area in another table

Question

as the title states, I'm trying to return a query which gets account details from the accounts table, gets the average rating for the account from the reviews table, and limits the rows to the service location associated to the account.

Here are the simplified tables:

accounts

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | John       | Smith     |
|  2 | Bob        | Doe       |
|  3 | Alice      | McLovin   |
|  4 | Bruce      | Wayne     |
+----+------------+-----------+

reviews

+----+-------------+-----+--------+
| id | acccount_id | ... | rating |
+----+-------------+-----+--------+
|  1 |           1 | ... |      9 |
|  2 |           1 | ... |     10 |
|  3 |           2 | ... |      7 |
|  4 |           1 | ... |      2 |
|  5 |           4 | ... |      6 |
+----+-------------+-----+--------+

service_area

+----+-------------+---------+
| id | acccount_id | city_id |
+----+-------------+---------+
|  1 |           1 |    1140 |
|  2 |           1 |    1001 |
|  3 |           2 |    1140 |
|  4 |           1 |    1086 |
|  5 |           4 |    1001 |
+----+-------------+---------+

For example, the user may request to view all accounts which have a service area of city_id 1140. The query should then return the first_name, last_name, and average rating for each account within the specified service area. Note that accounts can have multiple service areas (see service_area table).

Thanks in advance!

UPDATE:

The following QUERY did the trick! I needed a LEFT JOIN for the reviews table!

SELECT a.first_name, a.last_name, AVG(r.rating) avg_rating
FROM accounts a
JOIN service_area sa
ON a.id = sa.account_id AND sa.city_id = 1140
LEFT JOIN reviews r 
ON a.id = r.account_id
GROUP BY a.id

M Khalid Junaid M Khalid Junaid · Accepted Answer · 2015-10-01T18:58:30

You can use joins and simple aggregation with group by

SELECT a.*,
AVG(r.rating) avg_rating
FROM accounts a
JOIN reviews r ON a.id = r.acccount_id
JOIN service_area s ON a.id = s.acccount_id
WHERE s.city_id = 1140
GROUP BY a.id

Result set will be like

    id  first_name  last_name  avg_rating  
------  ----------  ---------  ------------
     1  John        Smith            7.0000
     2  Bob         Doe              7.0000

Use LEFT join for when there are no reviews available

SELECT a.*,
COALESCE(AVG(r.rating),0) avg_rating
FROM accounts a
LEFT JOIN reviews r ON a.id = r.acccount_id
JOIN service_area s ON a.id = s.acccount_id
WHERE s.city_id = 1140
GROUP BY a.id

DEMO

MySQL return distinct accounts from one table, average rating from another, all based on service area in another table

1 Answers