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

I’m creating a Library Management System and in the database I want to display book ISBN, Title and No. of copies of each book with their located library.

Here’s the code I tried :

CREATE VIEW library_information 
AS  
SELECT b.ISBN, b.Title, COUNT(CopyNumber) AS NoOfCopies, l.Name AS Location,  l.MainName AS MainBranch
FROM Book b
INNER JOIN Copy c ON c.CBookISBN = b.ISBN 
INNER JOIN Branch l ON l.Name = c.LibraryName
GROUP BY b.ISBN;

It gives me the error Column ‘Book.Title’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Some tips on solving this?

Answer

One simple rule: You can not use the actual column without any aggregation in the SELECT clause which is not a part of the GROUP BY.

So in your query, It should be:

CREATE VIEW library_information 
AS  
SELECT b.ISBN, -- unaggregated column - must be in GROUP BY clause
       b.Title, -- unaggregated column - must be in GROUP BY clause
       COUNT(CopyNumber) AS NoOfCopies, 
       l.Name AS Location, -- unaggregated column - must be in GROUP BY clause
       l.MainName AS MainBranch -- unaggregated column - must be in GROUP BY clause
FROM Book b
INNER JOIN Copy c ON c.CBookISBN = b.ISBN 
INNER JOIN Branch l ON l.Name = c.LibraryName
GROUP BY b.ISBN,
         b.Title, -- added this and following columns in GROUP BY
         l.Name,
         l.MainName;