0

I'm sending the next request in my data base:

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;

And getting the next error:

Column 'Book.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

How do I change my request to make it work?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Volodymyr Humeniuk
  • 3,411
  • 9
  • 35
  • 70
  • Are you sure you are working in mysql? You have this tagged as mysql and sql server. The error message is a sql server message. – Sean Lange Sep 03 '19 at 21:11
  • Possible duplicate of [GROUP BY / aggregate function confusion in SQL](https://stackoverflow.com/questions/4611897/group-by-aggregate-function-confusion-in-sql) – GMB Sep 03 '19 at 21:11
  • Regardless of the DBMS you are actually using you should consider using ANSI-92 style joins. They have been around now for more than 25 years. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins – Sean Lange Sep 03 '19 at 21:12
  • 1
    I am not sure how much more clear the error message can possibly be. You can't have a scalar value from a column in a query when there are aggregates without that column being used in an aggregate OR in the group by. – Sean Lange Sep 03 '19 at 21:13
  • 1
    Count is a aggregation fuction, so write at the end the group by Book.Name and look if the result is correct – nbk Sep 03 '19 at 21:14

2 Answers2

1

if this is SQL Server, try this

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;

Or better:

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;
Yuri
  • 2,820
  • 4
  • 28
  • 40
0

I'd format it like this:

SELECT [Book] = b.Name
    , [Amount orders] = Count(o.ShopperId) 
FROM Book as b 
INNER JOIN [dbo].[Order] as o
    ON b.BookId = o.BookId 
WHERE o.Amount = 5
GROUP BY b.Name;
Slava Murygin
  • 1,951
  • 1
  • 10
  • 10