Syntax Error Database SQL

Question

1.00/5 (1 vote)

See more:

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.

Here are the tables we are working on.

HOTEL (HotelNo, HotelName, City)

ROOM_TYPE (RoomType, Descr, RoomRate)

ROOM (HotelNo, RoomNo, RoomType, PhoneExt)
FK1: Foreign key HotelNo references HOTEL
FK2: Foreign key RoomType references ROOM_TYPE
GUEST (GuestNo, FirstName, LastName, Address, City, State, ZipCode)
GUEST_PHONE (PhoneNumber, GuestNo, PhoneType)
FK: Foreign key GuestNo references GUEST

RESERVATION (ResNum, HotelNo, RoomNo, GuestNo, ArrivalDate, DepartureDate, NumPersons)
FK1: Foreign Key (HotelNo, RoomNo) references ROOM
FK2: Foreign Key GuestNo references GUEST

This is third SQL statement that retrieves everyone's name and the GuestNo who have made a reservation at the hotel. I don't know where to add the count function without getting a Syntax Error.

SQL

SELECT GUEST.FirstName,  COUNT (RESERVATION.GuestNo)
FROM GUEST INNER JOIN RESERVATION ON GUEST.GuestNo =RESERVATION.GuestNo
ORDER BY GUEST.FirstName
GROUP BY GUEST.GuestNo

This is giving me a Syntax error in my query expression GUEST.FirstName, GROUP BY GUEST.GuestNo. It was retrieving the names who have made reservations.

Posted 9-Mar-15 17:35pm

Member 11508350

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abdulnazark · Accepted Answer · 2015-03-11T07:11:00

Solution 3

solution 2 only to change group by

SQL

SELECT GUEST.FirstName, Count(RESERVATION.GuestNo) AS CountOfGuestNo
FROM GUEST INNER JOIN RESERVATION ON GUEST.GuestNo = RESERVATION.GuestNo
GROUP BY GUEST.FirstName
ORDER BY GUEST.FirstName

Posted 11-Mar-15 7:11am

Abdulnazark

Updated 11-Mar-15 7:13am

v2

Member 11508350 · Accepted Answer · 2015-03-09T18:05:00

SELECT COUNT(RESERVATION.GuestNo), GUEST.FirstName, RESERVATION.GuestNo

FROM GUEST INNER JOIN RESERVATION on GUEST.GuestNo = RESERVATION.GuestNo

GROUP BY GUEST.FirstName

I've tried this combination as well but Access tells me, You tried to execute a query that does not include the specified expression 'GuestNo' as part of an aggregate functio

Praneet Nadkar · Accepted Answer · 2015-03-09T18:05:00

Solution 2

Try this:

SQL

SELECT GUEST.FirstName,  COUNT (RESERVATION.GuestNo)
FROM GUEST INNER JOIN RESERVATION ON GUEST.GuestNo =RESERVATION.GuestNo
GROUP BY GUEST.GuestNo
ORDER BY GUEST.FirstName;

Posted 9-Mar-15 18:05pm

Praneet Nadkar

Comments

[no name] 10-Mar-15 0:10am

It gave me the same thing rather than GuestNo its FirstName.