Question

I am using oracle sql developer to create some queries to generated reports and it is not working.

I am not sure how to use count, group by, and order by.

Help me fix my query and explain to me how you did, so can do it next time.

Also, I have to convert this query to a stored procedure, so can you help me do it too?

Here is my query:

SELECT COUNT(GUEST.FIRSTNAME), GUEST.FIRSTNAME, GUEST.LASTNAME, GUEST.GUESTTYPE,

RESERVATION.RESERVATIONDATE, GUEST.EMAIL,

FROM (((GUEST

INNER JOIN ROOMRENTAL ON GUEST.GUESTID = ROOMRENTAL.GUESTID)

INNER JOIN DISABLEFRIENDLY ON ROOMRENTAL.ROOMID = DISABLEFRIENDLY.DFROOMID)

INNER JOIN RESERVATION ON GUEST.GUESTID = RESERVATION.GUESTID)

WHERE RESERVATION.RESERVATIONDATE >= '15-DEC-2018'

GROUP BY GUEST.GUESTID

ORDER BY GUEST.GUESTID;

===========================

============================

I UPDATED THE TABLES!

============================

============================

Here is the information required:

CREATE TABLE Guest

(

GuestID NUMBER(10) NOT NULL,

FirstName VARCHAR2(25) NOT NULL,

LastName VARCHAR2(25) NOT NULL,

NumberStreet VARCHAR2(25) NOT NULL,

City VARCHAR2(20) NOT NULL,

StateName VARCHAR2(20) NOT NULL,

ZipCode NUMBER(5) NOT NULL,

Country VARCHAR2(20) NOT NULL,

DateOfBirth DATE NOT NULL,

Age NUMBER(3) NOT NULL,

Gender VARCHAR2(1) NOT NULL,

MobileNumber NUMBER(10) NOT NULL,

Email CHAR(30) NULL,

GuestType VARCHAR2(10) NOT NULL,

CONSTRAINT GuestID_PK PRIMARY KEY (GuestID)

);

========================

========================

CREATE TABLE RoomRental

(

GuestID NUMBER (10) NOT NULL,

ReservationNumber NUMBER (10) NOT NULL,

RoomID NUMBER (10) NOT NULL,

CheckInDate DATE NOT NULL,

CheckInTime NUMBER (4) NOT NULL,

CheckOutDate DATE NOT NULL,

CheckOutTime NUMBER (4) NOT NULL,

CONSTRAINT G_R_R_PK PRIMARY KEY (GuestID, ReservationNumber, RoomID)

);

===========================

===========================

CREATE TABLE DisableFriendly

(

DFRoomID NUMBER (10) NOT NULL,

WheeledShowerChairAvailability VARCHAR2 (1) NOT NULL,

CONSTRAINT DFRoomID_PK PRIMARY KEY (DFRoomID)

);

=========================

=========================

CREATE TABLE Reservation

(

ReservationNumber Number(10) NOT NULL,

GuestID NUMBER(10) NOT NULL,

HotelID NUMBER(10) NOT NULL,

LocationID NUMBER(10) NOT NULL,

ReservationDate DATE NOT NULL,

DateIn DATE NOT NULL,

TimeIn NUMBER(4) NOT NULL,

DateOut DATE NOT NULL,

TimeOut NUMBER(4) NOT NULL,

NumberOfNights NUMBER(3) NOT NULL,

NumberOfRooms NUMBER(2) NOT NULL,

CONSTRAINT ReservationNumber_PK PRIMARY KEY (ReservationNumber)

);

=====================================

INSERT INTO Guest

(GuestID, FirstName, LastName, NumberStreet, City, StateName, ZipCode, Country,

DateOfBirth, Age, Gender, MobileNumber, Email, GuestType)

VALUES (5051003, 'Clark', 'Kent', '103 Orange Street', 'New York', 'New York', 10019, 'US',

TO_DATE('10-Nov-1971', 'DD-MON-YYYY'), 47, 'M', 2122200002, 'clarkkentmail.com', 'Corporate');

========================================

INSERT INTO RoomRental (GuestID, ReservationNumber, RoomID, CheckInDate, CheckInTime, CheckOutDate, CheckOutTime)

VALUES (5051003, 89981003, 80055001, TO_DATE('12-Apr-2019', 'DD-MON-YYYY'), 1500, TO_DATE('21-Apr-2019', 'DD-MON-YYYY'), 1100);

==========================================

INSERT INTO DisableFriendly (DFRoomID, WheeledShowerChairAvailability) VALUES (80055001, 'Y');

==========================================

INSERT INTO Reservation (ReservationNumber, GuestID, HotelID, LocationID, ReservationDate, DateIn,

TimeIn, DateOut, TimeOut, NumberOfNights, NumberOfRooms)

VALUES (89981003, 5051003, 70001, 9000700, TO_DATE('17-Dec-2018', 'DD-MON-YYYY'),

TO_DATE('17-Dec-2018', 'DD-MON-YYYY'), 1500, TO_DATE('19-Dec-2018', 'DD-MON-YYYY'), 1100, 2, 1);

Answer 1

SELECT T1.Total, T2.FIRSTNAME, T2.LASTNAME, T2.GUESTTYPE, T2.RESERVATIONDATE, T2.EMAIL
FROM (
SELECT COUNT(GUEST.FIRSTNAME) Total, GUEST.GUESTID AS GUESTID
FROM GUEST
GROUP BY GUEST.GUESTID ) T1
JOIN
(
SELECT GUEST.GUESTID AS GUESTID
   , GUEST.FIRSTNAME AS FIRSTNAME
   , GUEST.LASTNAME AS LASTNAME
   , GUEST.GUESTTYPE AS GUESTTYPE
   , RESERVATION.RESERVATIONDATE AS RESERVATIONDATE
   , GUEST.EMAIL AS EMAIL
FROM GUEST
INNER JOIN ROOMRENTAL
ON ( GUEST.GUESTID = ROOMRENTAL.GUESTID )
INNER JOIN DISABLEFRIENDLY
   ON ( ROOMRENTAL.ROOMID = DISABLEFRIENDLY.DFROOMID )
INNER JOIN RESERVATION
   ON ( GUEST.GUESTID = RESERVATION.GUESTID AND RESERVATION.RESERVATIONDATE >= '15-DEC-2018' )
) T2
ON (T1.GUESTID = T2.GUESTID)
ORDER BY T1.GUESTID;

ERROR :
[1] The previous query is incorrect because for group by function and other column in select query must include in group by column.
[2] The previous query in incomplete because opening and closing bracket are not properly

Solution
[1] Split previous query into 2 sub Query.
First Sub query get the count based on GUESTID group by.
   Second query remain same as previous on group by thing remove here.
[2] Join both the sub query using GUESTID and then order by them.