时间戳

首页 > 解决方案 > 将计算的 COUNT 列从一个视图添加到另一个视图

mysql - 将计算的 COUNT 列从一个视图添加到另一个视图

问题描述

我有一个视图，它引用了一个带有供应商联系信息的供应商表和一个 vendor_ratings 表来显示每个供应商的平均评级。该视图在提取供应商信息和评级平均值时有效，但我有另一个视图可以告诉我每个供应商被评级的次数。这个视图工作正常，但我无法让它在我的加入视图中工作。

这是适用于供应商信息和平均评级的视图代码：

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 OUTER JOIN `vendor ratings` ON vendors.Vendor = `vendor ratings`.Vendor
GROUP BY vendor

这是我另一个视图的代码，它显示了每个供应商被评分的次数：

SELECT
Vendor,
COUNT(Vendor) AS `COUNT(Vendor)`
FROM `vendor ratings`
GROUP BY Vendor
ORDER BY Vendor

我尝试将以下行添加： COUNT(vendor As COUNT(Vendor), 到已经适用于其他所有内容但没有成功的代码中。我做错了什么？我只是结果看起来像这样......

供应商、平均评级、评级次数、电话号码等......

标签： mysqljoincountformulaaverage

解决方案

Do the summarization before you join (i.e. use a subquery or "derived table")

SELECT
      v.*
    , vr.*
FROM
FROM vendors v
LEFT JOIN (
     SELECT
            vendors.Vendor
          , COUNT(*)                                    AS `COUNT(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`
          , 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 `vendor ratings`
     GROUP BY vendors.Vendor
     ) vr ON v.Vendor = vr.Vendor

Please note that MySQL has a very unfortunate way of allowing group by clauses to have only a few of the non-aggregating columns that exist in the select clause. This is NOT good practice and it can lead to "unexpected results". Slowly MySQL is improving this area but you should be alert to this problem here's a start. Research only_full_group_by

  • please note I do NOT recommend using select v.*, vr.* in your final code, list out the columns you need, I have done this only to focus attention on the subquery
  • but I DO recommend that you prefix ALL columns after the join by the relevant table prefix.

推荐阅读