CODEWITHSUNDEEP
mysqlms-access
Sam2704
Sam2704

Reputation: 81

Count Function in Access SQL Query

List the guests by name and the number of times each has reserved a room at one of our hotels. Arrange the list in order from most-frequent to least-frequent guest.

I'm keep getting aggregate function for Firstname and LastName

So Far i have this code

SELECT FirstName, LastName, Count(ResNum) AS TotalReservations
FROM RESERVATION, GUEST
Where GUEST.GuestNo = RESERVATION.GuestNo
ORDER BY RESERVATION.GuestNo

And here is the link for the RelationShip Table

View Relationship Table <--- LINK

Upvotes: 1

Views: 196

Answers (2)

Linger
Linger

Reputation: 15048

SELECT g.FirstName, g.LastName, Count(r.ResNum) AS TotalReservations
FROM RESERVATION AS r
INNER JOIN GUEST AS g ON g.GuestNo = r.GuestNo
GROUP BY g.FirstName, g.LastName
ORDER BY Count(r.ResNum) DESC

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

Try this:

SELECT FirstName, LastName, Count(ResNum) AS TotalReservations
FROM RESERVATION
INNER JOIN GUEST ON GUEST.GuestNo = RESERVATION.GuestNo
GROUP BY FirstName, LastName
ORDER BY COUNT(ResNum) DESC

Upvotes: 1

Related Questions