sql - AVG的AVG，子查询的聚合函数

Question

此子查询生成正确的表。但是现在我想获得平均值的平均值，并且出现错误“表“c”的 FROM 子句条目丢失”。

SELECT
   c.name,
   AVG(avgvalue)
FROM
(
SELECT
  c.name, 
  p.name,
  AVG(a."value") AS avgvalue
FROM answers a INNER JOIN survey_responses sr ON sr.id = a.survey_response_id AND a.question_id = 13
  INNER JOIN answers category_answer ON category_answer.survey_response_id =  sr.id AND category_answer.question_id = 264
  INNER JOIN answers_categories ac ON category_answer.id = ac.answer_id
  INNER JOIN categories c ON c.id = ac.category_id
  INNER JOIN products p ON p.id = a.product_id
WHERE c.name IN ('Accounting') 
GROUP BY c.name, p."name"
HAVING count(p.name)>10
) as ProductAverages
GROUP BY c.name;

Answer 1

您正在命名 ProductAverages，因此您的表别名应该引用它，而不是 c - 它只能在内部查询中使用:

SELECT
   name, -- Here
   AVG(avgvalue)
FROM
(
SELECT
  c.name, 
  p.name,
  AVG(a."value") AS avgvalue
FROM answers a INNER JOIN survey_responses sr ON sr.id = a.survey_response_id AND a.question_id = 13
  INNER JOIN answers category_answer ON category_answer.survey_response_id =  sr.id AND category_answer.question_id = 264
  INNER JOIN answers_categories ac ON category_answer.id = ac.answer_id
  INNER JOIN categories c ON c.id = ac.category_id
  INNER JOIN products p ON p.id = a.product_id
WHERE c.name IN ('Accounting') 
GROUP BY c.name, p."name"
HAVING count(p.name)>10
) as ProductAverages
GROUP BY name; -- and here