SELECT from multiple tables with GROUP BY

Question

I have a little problem with multi-user request. (RDBMS: Access)

Here is the database schema: (only S_Cards, Books, Authors, Students tables are used in this query) S_Cards - the order of students' books (in the library).

Query: Select the most popular authors (s) among students and the number of books by this author that have been ordered in the library.

Although I can get a list of orders + authors like this in one query:

SELECT Students.FirstName & " " & Students.LastName AS [Student], Books.Name AS [Book], Authors.FirstName & " " & Authors.LastName AS [Author] FROM Students, Books, S_Cards, Authors WHERE S_Cards.ID_Student = Students.ID AND S_Cards.ID_Book = Books.ID AND Books.ID_Author = Authors.ID ORDER BY Authors.LastName 

Result (sorry, this is in Russian):

I cannot understand why I cannot use COUNT and GROUP BY as follows:

 SELECT Students.FirstName & " " & Students.LastName AS [Student], Books.Name AS [Book], COUNT(Authors.FirstName & " " & Authors.LastName) AS [Number of books] FROM Students, Books, S_Cards, Authors WHERE S_Cards.ID_Student = Students.ID AND S_Cards.ID_Book = Books.ID AND Books.ID_Author = Authors.ID GROUP BY 3 

I get the error "Authors.FirstName" and "Authors.LastName" is not part of a static function or group.

Questions:

• Is there a way to make this query without a JOIN, only using GROUP BY, SELECT, UNION, and how?
• What is the problem in my second request?

Answer 1

Solution (extracted from the old version ):

 SELECT TOP 1 Author, COUNT(Book) AS [Number of books] FROM ( SELECT Students.FirstName & " " & Students.LastName AS [Student], Books.Name AS [Book], Authors.FirstName & " " & Authors.LastName AS [Author] FROM Students, Books, S_Cards, Authors WHERE S_Cards.ID_Student = Students.ID AND S_Cards.ID_Book = Books.ID AND Books.ID_Author = Authors.ID ORDER BY Authors.LastName ) GROUP BY Author ORDER BY 2 DESC 

Answer 2

String concatenation operator in SQL Server + not & . In addition, you should group things that are not an aggregate function.

 SELECT Students.FirstName + ' ' + Students.LastName AS [Student] , Books.Name AS [Book] , COUNT(Authors.FirstName + ' ' + Authors.LastName) AS [Number of books] FROM Students JOIN S_Cards ON S_Cards.ID_Student = Students.ID JOIN BOOKS ON S_Cards.ID_Book = Books.ID JOIN Authors ON Books.ID_Author = Authors.ID GROUP BY Students.FirstName + ' ' + Students.LastName , Books.Name 

Please note that I changed your request to the standard ANSI connection syntax, which makes errors much more complicated and easier to read.

Thinking about this, your graph seems a little strange. Isn't the number of COUNT(Books.ID) books COUNT(Books.ID) ?

Answer 3

You must GROUP BY something that is not part of the aggregate function:

 SELECT Students.FirstName & " " & Students.LastName AS [Student], Books.Name AS [Book], COUNT(Authors.FirstName & " " & Authors.LastName) AS [Number of books] FROM Students, Books, S_Cards, Authors WHERE S_Cards.ID_Student = Students.ID AND S_Cards.ID_Book = Books.ID AND Books.ID_Author = Authors.ID GROUP BY Students.FirstName & " " & Students.LastName, Books.Name AS [Book] 

I suggest you start using explicit rather than implicit joins. There is a better choice for MS Access, for the most part.

 <...> FROM Students INNER JOIN S_Cards ON Students.ID = S_Cards.ID_Student 

or

 <...> FROM Students LEFT JOIN S_Cards ON Students.ID = S_Cards.ID_Student 

The query project window allows you to create a JOIN with the correct syntax. Just drag the join fields from one table to another and select the type of join you want.