When doing a left join with vendors table & vendor ratings table only one record shows up

Question

I have a vendors table and a vendor ratings table. I am trying to create a view that shows ALL vendors on left and their overall rating on the right. I would like the vendors to show up even if they have not been rated yet. I'm using mysql 5.7. This is my query:

SELECT
vendors.ID,
vendors.Vendor,
ROUND(((AVG(`Cost_Rating`) + AVG(`Documentation_Rating`) + AVG(`Safety_Rating`) + AVG(`Equipment_Rating`) + AVG(`Performance_Rating`) + AVG(`Promptness_Rating`) + AVG(`Communication_Rating`))/7.0),2) as `Overall Rating`,
vendors.`Phone #`,
vendors.`Fax #`,
vendors.Website,
vendors.`Physical Address`,
vendors.`P.O. Box`,
vendors.City,
vendors.`State`,
vendors.Zip,
vendors.`Region Serving`,
vendors.Note,
vendors.OnVendorList,
vendors.`Search Words`,
ROUND(AVG(`Communication_Rating`),2) AS `Average Communication Rating`,
ROUND(AVG(`Promptness_Rating`),2) AS `Average Promptness Rating`,
ROUND(AVG(`Performance_Rating`),2) AS `Average Performance Rating`,
ROUND(AVG(`Equipment_Rating`),2) AS `Average Equipment Rating`,
ROUND(AVG(`Safety_Rating`),2) AS `Average Safety Rating`,
ROUND(AVG(`Documentation_Rating`),2) AS `Average Documentation Rating`,
ROUND(AVG(`Cost_Rating`),2) AS `Average Cost Rating`
FROM vendors
LEFT JOIN `Vendor Ratings` ON vendors.vendor = `vendor ratings`.vendor

No matter what type of join I use I get the same results. Where is my problem?

You are missing `Group By vendors.vendor` at the end. Without group by it will simply calculate overall aggregate (for all the vendors) — Madhur Bhaiya, Oct 12 '18 at 19:02

score 0 · Answer 1 · answered Oct 12 '18 at 19:04

You are missing GROUP BY on vendor. Without the group by clause, the query is simply calculating overall aggregate values (all the vendors combined at once).

Try the following instead:

SELECT
vendors.ID,
vendors.Vendor,
ROUND(((AVG(`Cost_Rating`) + AVG(`Documentation_Rating`) + AVG(`Safety_Rating`) + AVG(`Equipment_Rating`) + AVG(`Performance_Rating`) + AVG(`Promptness_Rating`) + AVG(`Communication_Rating`))/7.0),2) as `Overall Rating`,
vendors.`Phone #`,
vendors.`Fax #`,
vendors.Website,
vendors.`Physical Address`,
vendors.`P.O. Box`,
vendors.City,
vendors.`State`,
vendors.Zip,
vendors.`Region Serving`,
vendors.Note,
vendors.OnVendorList,
vendors.`Search Words`,
ROUND(AVG(`Communication_Rating`),2) AS `Average Communication Rating`,
ROUND(AVG(`Promptness_Rating`),2) AS `Average Promptness Rating`,
ROUND(AVG(`Performance_Rating`),2) AS `Average Performance Rating`,
ROUND(AVG(`Equipment_Rating`),2) AS `Average Equipment Rating`,
ROUND(AVG(`Safety_Rating`),2) AS `Average Safety Rating`,
ROUND(AVG(`Documentation_Rating`),2) AS `Average Documentation Rating`,
ROUND(AVG(`Cost_Rating`),2) AS `Average Cost Rating`
FROM vendors
LEFT JOIN `Vendor Ratings` ON vendors.vendor = `vendor ratings`.vendor
GROUP BY 
  vendors.ID,
  vendors.Vendor,
  vendors.`Phone #`,
  vendors.`Fax #`,
  vendors.Website,
  vendors.`Physical Address`,
  vendors.`P.O. Box`,
  vendors.City,
  vendors.`State`,
  vendors.Zip,
  vendors.`Region Serving`,
  vendors.Note,
  vendors.OnVendorList,
  vendors.`Search Words`

Also, note that all the column(s) in the Select clause, which are not using aggregate functions like Avg(), have been added to the Group By clause as well.

Do Read: SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

When doing a left join with vendors table & vendor ratings table only one record shows up

1 Answers1