Aggregate function Count () in a query with multiple tables
There are two tables.
The first table Reader
contains information about the library's readers.
Table fields: LibraryCardNumber, ReaderSurname, ReaderName, ReaderPatronymic, BirthDate, Address, Phone
.
The second table BookDelivery
contains information about the issued books.
Table fields: BookID, ReaderLibraryCardNumber, DateStart, DateReturned
.
I need to display information about the readers and the number of books they have seen.
Using the aggregate function Count (), I found the number in this way:
SELECT ReaderLibraryCardNumber as [Номер читательского билета],
Count(*) as [Количество книг]
FROM BookDelivery
GROUP BY ReaderLibraryCardNumber;
But it is not enough for me to know only the number of the reader's ticket, I need another one information about the reader from the Reader table (for example, the first and last name of the reader). I tried to get it this way:
SELECT BookDelivery.ReaderLibraryCardNumber as [Номер читательского билета],
Reader.ReaderName as [Имя],
Reader.ReaderSurname as [Фамилия],
Count(*) as [Количество книг]
FROM BookDelivery, Reader
WHERE BookDelivery.ReaderLibraryCardNumber = Reader.LibraryCardNumber
GROUP BY ReaderLibraryCardNumber;
But Access swears: "Your request does not contain the specified 'Name' expression as part of the aggregate function." Is it possible to somehow formalize a request so that both the quantity is calculated and the fields from another table are output?
1 answers
SELECT Reader.LibraryCardNumber as [Номер читательского билета],
Reader.ReaderName as [Имя],
Reader.ReaderSurname as [Фамилия],
Count(*) as [Количество книг]
FROM BookDelivery, Reader
WHERE BookDelivery.ReaderLibraryCardNumber = Reader.LibraryCardNumber
GROUP BY Reader.LibraryCardNumber,
Reader.ReaderName,
Reader.ReaderSurname;