Hello,
Per the query below, I'm looking for help on how to apply a COUNT function (based off of "rentalno" within table RAGREEMENT) to identify the Outlet (returns the street address in the output) with the most rentals. Based off said count, I will need to rank the findings so that I can only output the rentals for the outlet with the most rentals (see desired output below).
I'm using Oracle 11g Express with SQL*Plus.
Please let me know if I need to further clarify what I'm trying to achieve.
I'd appreciate any help you can provide me. Thank you!
QUERY:
column client format a30;
SELECT rentalno “RENTAL#”, startdate “RENTED”, returndate “RETURNED”,
client.clientno || ' - ' || contact_last || ', ' ||
contact_first CLIENT, outstreet “OUTLET STREET ADR.”
FROM ragreement
JOIN client ON ragreement.clientno = client.clientno
JOIN vehicle ON ragreement.licenseno = vehicle.licenseno
JOIN outlet ON vehicle.outno = outlet.outno
ORDER BY rentalno;
CURRENT OUTPUT:
RENTAL# RENTED RETURNED CLIENT OUTLET STREET ADR.
---------- --------- --------- ------------------------------ ------------------
3000000001 03-JUL-13 08-JUL-13 2000000001 - Morgan, Gwen 213 Fast Run Rd
3000000002 04-JUL-13 07-JUL-13 2000000002 - Brenger, Frank 213 Fast Run Rd
3000000003 05-JUL-13 07-JUL-13 2000000003 - Pocian, Kathy 213 Fast Run Rd
3000000007 05-MAY-13 10-MAY-13 2000000007 - McKhee, Mark 1000 Bridge Water
3000000008 06-MAY-13 07-MAY-13 2000000008 - Smith, Carol 54 N. South St.
DESIRED OUTPUT:
RENTAL# RENTED RETURNED CLIENT OUTLET STREET ADR.
---------- --------- --------- ------------------------------ ------------------
3000000001 03-JUL-13 08-JUL-13 2000000001 - Morgan, Gwen 213 Fast Run Rd
3000000002 04-JUL-13 07-JUL-13 2000000002 - Brenger, Gary 213 Fast Run Rd
3000000003 05-JUL-13 07-JUL-13 2000000003 - Pocian, Carol 213 Fast Run Rd
SAMPLE DATA:
CREATE TABLE CLIENT
(clientNo NUMBER(10) PRIMARY KEY,
ClientName VARCHAR2(38),
Street VARCHAR2(18),
City VARCHAR2(20),
State VARCHAR2(2),
ZipCode VARCHAR2(5),
WebAddress VARCHAR2(38),
Contact_first VARCHAR2(38),
Contact_last VARCHAR2(38),
Phone VARCHAR2(12),
Email VARCHAR2(40));
CREATE TABLE RAGREEMENT
(RentalNo NUMBER(10) PRIMARY KEY,
StartDate DATE,
ReturnDate DATE,
MileageBefore NUMBER(10),
MileageAfter NUMBER(10),
InsuranceType VARCHAR2(25),
ClientNo NUMBER(10),
LicenseNo VARCHAR2(7));
CREATE TABLE VEHICLE
(LicenseNo VARCHAR2(7) PRIMARY KEY,
Make VARCHAR2(12),
Model VARCHAR2(15),
Color VARCHAR2(15),
Year NUMBER(4),
NoDoors NUMBER(2),
Capacity NUMBER(2),
DailyRate NUMBER(4),
outNo NUMBER(4));
CREATE TABLE OUTLET
(outNo NUMBER(4) PRIMARY KEY,
outStreet VARCHAR2(18) ,
outCity VARCHAR2(20),
outState VARCHAR2(2),
outZipCode VARCHAR2(5),
outPhone VARCHAR2(12),
ManagerNo NUMBER(6));
INSERT INTO CLIENT VALUES ('2000000001','University of Pittsburgh','213 Fast Run Rd','Houston','PA','68954','www.pitt.edu','Gwen','Morgan','7243441957','jfh12@pitt.edu');
INSERT INTO CLIENT VALUES ('2000000002','Zip Free','1000 Bridge Water','Dallas','WV','23659','www.zipfree.org','Frank','Brenger','9856587896','gary.branger@zipfree.org');
INSERT INTO CLIENT VALUES ('2000000003','Go Go Go, Inc.','54 N. South St.','Richmond','VA','97562','www.gogogo.com','Kathy','Pocian','3048965489','kpocian@gogogo.com');
INSERT INTO CLIENT VALUES ('2000000004','Carnegie Mellon University','1001 Dotted Way','Washington','DC','54865','www.cmu.edu','Linda','Betsch','7896541235','lbetsch2@cmu.edu');
INSERT INTO CLIENT VALUES ('2000000005','NSA','234 Jump St.','Pittsburgh','PA','21358','www.nsa.gov','Vicki','Klein','5986598785','vklein234@nsa.gov');
INSERT INTO CLIENT VALUES ('2000000006','FBI','3289 Cresent Ave.','York','WV','54268','www.fbi.gov','Mark','Severns','5965897458','mseverns@fbi.gov');
INSERT INTO CLIENT VALUES ('2000000007','Notes, Inc.','975 Sunset Blvd.','Canonsburg','PA','24973','www.notes.org','Mark','McKhee','7896589658','mcphee_david_m@notes.org');
INSERT INTO CLIENT VALUES ('2000000008','Caterpillar, Inc.','2045 West Pike St.','Cranberry','PA','36597','www.cat.com','Carol','Smith','5426589756','carol.smith@cat.com');
INSERT INTO CLIENT VALUES ('2000000009','Clues Kids','72 East Salmon St.','Wheeling','WV','78564','www.clues.com','Matt','Walker','7896541235','mwalker@clues.com');
INSERT INTO CLIENT VALUES ('2000000010','Free Lancer','678 Blaire Av.','Meadville','WV','85652','www.freelancer.org','Frank','Peer','2135689456','frank.peer@freelancer.org');
INSERT INTO RAGREEMENT VALUES ('3000000001','3-Jul-13','8-Jul-13','123000','123350','LDW','2000000001','BH05J9U');
INSERT INTO RAGREEMENT VALUES ('3000000002','4-Jul-13','7-Jul-13','234567','234917','CDW','2000000002','H4L0DH8');
INSERT INTO RAGREEMENT VALUES ('3000000003','5-Jul-13','7-Jul-13','576858','577208','Waived','2000000003','5HDI9TG');
INSERT INTO RAGREEMENT VALUES ('3000000004','7-Jun-13','11-Jun-13','623450','623800','CDW','2000000004','GI0UJD7');
INSERT INTO RAGREEMENT VALUES ('3000000005','8-Jun-13','15-Jun-13','123467','123817','LDW','2000000005','GJU4Y7D');
INSERT INTO RAGREEMENT VALUES ('3000000006','4-May-13','9-May-13','3456','5000','Waived','2000000006','S5BNP2S');
INSERT INTO RAGREEMENT VALUES ('3000000007','5-May-13','10-May-13','438','788','Waived','2000000007','XCH4Y23');
INSERT INTO RAGREEMENT VALUES ('3000000008','6-May-13','7-May-13','50','400','Waived','2000000008','A12F8GH');
INSERT INTO RAGREEMENT VALUES ('3000000009','2-Apr-13','7-Apr-13','45678','46028','LDW','2000000009','C4YUTSA');
INSERT INTO RAGREEMENT VALUES ('3000000010','3-Apr-13','18-Apr-13','7689','8039','CDW','2000000010','634HV2E');
INSERT INTO VEHICLE VALUES ('BH05J9U','Ford','Escape','White','2013','4','5','120','1001');
INSERT INTO VEHICLE VALUES ('H4L0DH8','Ford','Edge','Blue','2012','4','5','120','1001');
INSERT INTO VEHICLE VALUES ('5HDI9TG','Ford','Fiesta','Green','2013','4','5','90','1001');
INSERT INTO VEHICLE VALUES ('GI0UJD7','Ford','Fusion','Yellow','2012','4','5','90','1002');
INSERT INTO VEHICLE VALUES ('GJU4Y7D','Nissian','Versa','Teal','2012','4','5','90','1001');
INSERT INTO VEHICLE VALUES ('S5BNP2S','Nissian','Altima','Blue','2013','4','5','120','1003');
INSERT INTO VEHICLE VALUES ('XCH4Y23','Nissian','Maxima','Black','2012','4','5','120','1002');
INSERT INTO VEHICLE VALUES ('A12F8GH','Chevrolet','Cruze','Black','2013','4','5','90','1003');
INSERT INTO VEHICLE VALUES ('C4YUTSA','Chevrolet','Malibu','White','2012','4','5','120','1004');
INSERT INTO VEHICLE VALUES ('634HV2E','Chevrolet','Impala','Orange','2013','4','5','120','1004');
INSERT INTO OUTLET VALUES ('1001','213 Fast Run Rd','Houston','TX','68954','7245339865','100009');
INSERT INTO OUTLET VALUES ('1002','1000 Bridge Water','Dallas','TX','23659','4125469854','100008');
INSERT INTO OUTLET VALUES ('1003','54 N. South St.','Richmond','VA','97562','7458963215','100008');
INSERT INTO OUTLET VALUES ('1004','1001 Dotted Way','Washington','DC','54865','1459632514','100009');
INSERT INTO OUTLET VALUES ('1005','234 Jump St.','Pittsburgh','PA','21358','4153586452','100010');
INSERT INTO OUTLET VALUES ('1006','3289 Cresent Ave.','York','AZ','54268','1298745635','100010');
INSERT INTO OUTLET VALUES ('1007','975 Sunset Blvd.','Canonsburg','PA','24973','3698521475','100012');
INSERT INTO OUTLET VALUES ('1008','2045 West Pike St.','Cranberry','CA','36597','9658745896','100012');
INSERT INTO OUTLET VALUES ('1009','72 East Salmon St.','Wheeling','WV','78564','2365987456','100012');
INSERT INTO OUTLET VALUES ('1010','678 Blaire Av.','Meadville','OH','85652','1235897456','100007');