MYSQL SUM() returning top 10 values

I am trying to find the SUM of 7 numeric fields to find the total, COMBINED with a开发者_开发技巧n output to display a LIMIT of 10 results ORDER BY total of each SUM.

Here is my SUM code, which works on its own, but I am unsure how to use the two queries together, keep getting syntax errors.

SUM(comfort + service + ambience + friendliness + spacious + experience + toilets)/(7)/COUNT(shop_id) AS total FROM ratings GROUP BY shop_id

This is my SQL query to return data normally:

SELECT * FROM pubs, services, ratings
WHERE pubs.shop_id=services.shop_id AND pubs.shop_id=ratings.shop_id
GROUP BY shop_name
ORDER BY shop_name ASC

I do not have the total of the 7 values stored in my database, I am using the above SUM query to find the total. Thanks

---

Try to use

SELECT pubs.*, services.*, sum(temp.total) as final_total FROM pubs inner join services on pubs.shop_id=services.shop_id
inner join (
select 
SUM(comfort + service + ambience + friendliness + spacious + experience + toilets)/(7) /COUNT(shop_id) AS total, shop_id FROM ratings GROUP BY shop_id
) as temp on pubs.shop_id=temp.shop_id
GROUP BY shop_name
ORDER BY final_total DESC, shop_name ASC