mysql - 在 MYSQL Workbench 中使用计数和平均值的项目列表产生的结果太少

Question

我有一个书名列表，我需要首先计算每个书名的数量超过四本(按字母顺序)，然​​后计算书名总数并找出平均价格。但是，我的代码只生成 1 本书，而不是列表。这是我需要制作的:

Title   Count   Average Price    

ABC       6      15.95
DEF       6      21.67
GHI       7      11.47

这是我得到的:

Title   Count   Average Price    

ABC       98      15.95

这是我的代码:

SELECT 
Book_Title AS 'Title',
COUNT(Copy_Num) AS `Count`,
Round(Avg(Copy_Price), 2) AS "Average Price"
FROM BOOK
JOIN COPY USING (Book_Code)
JOIN BRANCH USING (Branch_Num)
HAVING Count(Copy_Num) > 4
ORDER BY Book_Title;

我错过了什么？是附加代码还是顺序错误？

预先感谢您的回复，我尝试的其他所有方法都产生了语法错误。

Answer 1

您缺少 GROUP BY Book_Title 语句。

SELECT 
  Book_Title AS 'Title',
  COUNT(Copy_Num) AS 'Count',
  Round(Avg(Copy_Price), 2) AS 'Average Price'
FROM BOOK
  JOIN COPY USING (Book_Code)
  JOIN BRANCH USING (Branch_Num)
GROUP BY Book_Title
HAVING Count(Copy_Num) > 4
ORDER BY Book_Title;