Count Function in Access SQL Query

Question

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

You're lacking a group by clause. And Access' underlying SQL engine will require that any field not being aggregated be listed in the group by. — Marc B
– Marc B, Commented Nov 12, 2013 at 19:13

Filipe Silva · Accepted Answer · 2013-11-12 19:15:26Z

1

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

answered Nov 12, 2013 at 19:15

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Linger · Accepted Answer · 2013-11-12 19:26:34Z

0

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

edited Nov 12, 2013 at 19:26

answered Nov 12, 2013 at 19:15

Linger

15.1k23 gold badges54 silver badges79 bronze badges

Collectives™ on Stack Overflow

Count Function in Access SQL Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related