CODEWITHSUNDEEP
sqlms-accesscountgroup-by
Aremyst
Aremyst

Reputation: 1528

SELECT from multiple tables with GROUP BY

I've a got a little problem with multitable query. (RDBMS: Access)

Here is the database schema: (only S_Cards, Books, Authors, Students tables are used in this query) S_Cards is Student book order (in library).

DB Scheme

Query: Select the most popular author(s) among students and the number of this author's books, which were ordered in library.

Although I can get 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, it's in Russian):

Query result

I can't figure out, why I can't COUNT and GROUP BY like this:

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 an error that 'Authors.FirstName & " " & Authors.LastName' is not a part of static function or group.

Questions:

  1. Is there a way to do this query without JOIN, only by GROUP BY, SELECT, UNION and how?
  2. What's the problem in my 2nd query?

Upvotes: 6

Views: 75756

Answers (3)

Pang
Pang

Reputation: 10127

Solution (extracted from older revision in question):

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

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91376

You must GROUP BY anything that is not part of 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, 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 are 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 design window will allow you to build your JOIN with the correct syntax. Just drag and drop the join fields from one table to the next and choose the join type you need.

Upvotes: 0

Ben
Ben

Reputation: 52923

The string concatenation operator in SQL Server is + not &. Also, you should group by 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've changed your query to the standard ANSI join syntax, which makes errors that much harder to make and is a lot easier to read.

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

Upvotes: 0

Related Questions