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

Question

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?

The error msg is pretty clear. `b.Title` should either be in `group by` or it should be an aggregated column in `select` list — Arun Palanisamy, Dec 28 '20 at 10:26
Think about how your result would look like. You are asking for one row per book type, but want to see different libraries with copies of that book on different rows. — GSerg, Dec 28 '20 at 10:28
Does this answer your question? [GROUP BY / aggregate function confusion in SQL](https://stackoverflow.com/questions/4611897/group-by-aggregate-function-confusion-in-sql) — Dale K, Dec 30 '20 at 01:49

score 2 · Accepted Answer · answered Dec 28 '20 at 10:32

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;

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

1 Answers1