sql-server - 列“Book.Name”在选择列表中无效，因为它不包含在任一聚合函数中

Question

我正在我的数据库中发送下一个请求：

Select Book.Name as 'Book', Count([dbo].[Order].ShopperId) as 'Amount orders'
from Book, [dbo].[Order]
where Book.BookId = [dbo].[Order].BookId and [dbo].[Order].Amount = 5;

并得到下一个错误：

列“Book.Name”在选择列表中无效，因为它既不包含在聚合函数中，也不包含在 GROUP BY 子句中

如何更改我的请求以使其生效？

标签： sql-server

Answer 1

如果这是 SQL Server，试试这个

Select Book.Name as 'Book', 
Count([dbo].[Order].ShopperId) as 'Amount orders' 
from Book, [dbo].[Order] 
where Book.BookId = [dbo].[Order].BookId and [dbo].[Order].Amount = 5
GROUP BY Book.Name;

或更好：

Select B.Name as 'Book', 
Count(o.ShopperId) as 'Amount orders' 
from [dbo].Book b JOIN [dbo].[Order] o ON b.BookId = o.BookId
where o.Amount = 5
GROUP BY Book.Name;