Home sql Count () aggregate function in a query with multiple tables

Count () aggregate function in a query with multiple tables

Author

Date

Category

There are two tables.

The first Table Reader contains information about the readers of the library.
Table fields: LibraryCardNumber, Readersurname, Readername, Readerpatronymic, Birthdate, Address, Phone .

The second table BookDelivery contains information about the books issued.
Table fields: Bookid, ReaderLibraryCardNumber, DateStart, DateretURned .

I need to withdraw information about readers and the number of books seen by him.

Using the Count () aggregate function, I found the number thus:

select readerlibrarycardnumber as [reader's number],
    Count (*) as [Number of books]
From BookDelivery
Group by readerlibraryCardNumber;

But it is not enough to know only the reader’s number, you need other information about the reader table (for example, the name and surname of the reader). Tried to get it in this way:

select bookdelivery.ReaderLibraryCardNumber AS [reader's number],
    Reader.Readername as [Name],
    Reader.Readersurname as [surname],
    Count (*) as [Number of books]
From BookDelivery, Reader
Where BookDelivery.ReaderLibraryCardNumber = Reader.LibraryCardNumber
Group by readerlibraryCardNumber;

But Access swears: “Your request does not contain the specified expression” name “as part of the aggregate function.” Is it possible to somehow make a request so that the quantity is calculated, and the fields from another table were detected?


Answer 1, Authority 100%

select reader.libraryCardNumber AS [reader's number],
    Reader.Readername as [Name],
    Reader.Readersurname as [surname],
    Count (*) as [Number of books]
From BookDelivery, Reader
Where BookDelivery.ReaderLibraryCardNumber = Reader.LibraryCardNumber
Group by reader.libraryCardNumber,
     Reader.Readername
     Reader.Readersurname;

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions