CODEWITHSUNDEEP
sql
Code_Ed_Student
Code_Ed_Student

Reputation: 1190

Calculate avg of column and display result if less than x number

I have a table named marina_slip. I have been give the following scenario: "Using MARINA_SLIP, in length sequence, display length and average rental fee for lengths with the average rental fee less than $3,500.00.". However, with my query below i am getting no the desired result and error or "invalid use of group function". How can I get the above scenario achieved? SQL FIDDLE

QUERY

SELECT Length, AVG(Rental_FEE) AS Lengths_Average_Rental_Fee FROM Marina_Slip WHERE AVG(Rental_Fee) < 3500;

Table Schema

TABLE MARINA_SLIP
(SLIP_ID DECIMAL(4,0) PRIMARY KEY,
MARINA_NUM CHAR(4),
SLIP_NUM CHAR(4),
LENGTH DECIMAL(4,0),
RENTAL_FEE DECIMAL(8,2),
BOAT_NAME CHAR(50),
BOAT_TYPE CHAR(50),
OWNER_NUM CHAR(4) );
CREATE TABLE OWNER
(OWNER_NUM CHAR(4) PRIMARY KEY,
LAST_NAME CHAR(50),
FIRST_NAME CHAR(20),
ADDRESS CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(5) );

VALUES
('2','Alexamara Central','283 Branston','W. Brinman','FL','32274');
INSERT INTO MARINA_SLIP
VALUES
(1,'1','A1',40,3800.00,'Anderson II','Sprite 4000','AN75');
INSERT INTO MARINA_SLIP
VALUES
(2,'1','A2',40,3800.00,'Our Toy','Ray 4025','EL25');
INSERT INTO MARINA_SLIP
VALUES
(3,'1','A3',40,3600.00,'Escape','Sprite 4000','KE22');
INSERT INTO MARINA_SLIP
VALUES
(4,'1','B1',30,2400.00,'Gypsy','Dolphin 28','JU92');
INSERT INTO MARINA_SLIP
VALUES
(5,'1','B2',30,2600.00,'Anderson III','Sprite 3000','AN75');
INSERT INTO MARINA_SLIP
VALUES
(6,'2','1',25,1800.00,'Bravo','Dolphin 25','AD57');
INSERT INTO MARINA_SLIP
VALUES
(7,'2','2',25,1800.00,'Chinook','Dolphin 22','FE82');
INSERT INTO MARINA_SLIP
VALUES
(8,'2','3',25,2000.00,'Listy','Dolphin 25','SM72');
INSERT INTO MARINA_SLIP
VALUES
(9,'2','4',30,2500.00,'Mermaid','Dolphin 28','BL72');
INSERT INTO MARINA_SLIP
VALUES
(10,'2','5',40,4200.00,'Axxon II','Dolphin 40','NO27');
INSERT INTO MARINA_SLIP

Upvotes: 0

Views: 358

Answers (1)

amow
amow

Reputation: 2223

SELECT Length, AVG(Rental_FEE) AS Lengths_Average_Rental_Fee FROM Marina_Slip
group by Length having AVG(Rental_Fee) < 3500 order by Length;

If you want the length column by desc order. put desc at last.

Upvotes: 3

Related Questions