Have tried the following, but error for not a single-group group function on lin
ID: 3603484 • Letter: H
Question
Have tried the following, but error for not a single-group group function on line 1, column 19: where S.description.
SELECT C.company, S.description, M.manu_name, SUM(I.quantity), SUM(I.quantity*I.total_price)
2 FROM customer C, orders O, items I, stock S, manufact M
3 WHERE C.customer_num=O.customer_num AND O.order_num=I.order_num
AND S.manu_code=M.manu_code
4 GROUP BY C.company
5 ORDER BY S.description;
Requirements:
For each customer that has placed an order, list each:
• The company name
• The item Description
• The manufacturer
• The quantity ordered
• The total price paid.
Include the following columns in the order given below:
From Customer Table: Company
From Stock Table: Description
From the Manufact Table: Manu_Name
From the Items Table: Quantity, Total Price
Order the output by Company and Description.
From the following data:
CREATE TABLE manufact
(manu_code CHAR(3) CONSTRAINT manu_code_pk PRIMARY KEY,
manu_name VARCHAR2(15))
INSERT INTO manufact
VALUES ('ANZ','Anza');
INSERT INTO manufact
VALUES ('HSK','Husky');
INSERT INTO manufact
VALUES ('HRO','Hero');
INSERT INTO manufact
VALUES ('NRG','Norge');
INSERT INTO manufact
VALUES ('SMT','Smith');
CREATE TABLE stock
(stock_num NUMBER(2),
manu_code CHAR(3),
description VARCHAR2(15),
unit_price NUMBER(8,2),
unit CHAR(4),
unit_descr VARCHAR2(15),
PRIMARY KEY (stock_num,manu_code),
FOREIGN KEY (manu_code)
REFERENCES manufact (manu_code))
INSERT INTO stock
VALUES (1,'HRO','baseball gloves',250.00,'case','10 gloves/case');
INSERT INTO stock
VALUES (1,'HSK','baseball gloves',800.00,'case','10 gloves/case');
INSERT INTO stock
VALUES (1,'SMT','baseball gloves',450.00,'case','10 gloves/case');
INSERT INTO stock
VALUES (2,'HRO','baseball',126.00,'case','24/case');
INSERT INTO stock
VALUES (3,'HSK','baseball bat',240.00,'case','12/case');
INSERT INTO stock
VALUES (4,'HSK','football',960.00,'case','24/case');
INSERT INTO stock
VALUES (4,'HRO','football',480.00,'case','24/case');
INSERT INTO stock
VALUES (5,'NRG','tennis racquet',28.00,'each','each');
INSERT INTO stock
VALUES (5,'SMT','tennis racquet',25.00,'each','each');
INSERT INTO stock
VALUES (5,'ANZ','tennis racquet',19.80,'each','each');
INSERT INTO stock
VALUES (6,'SMT','tennis ball',36.00,'case','24 cans/case');
INSERT INTO stock
VALUES (6,'ANZ','tennis balls',48.00,'case','24 cans/case');
INSERT INTO stock
VALUES (7,'HRO','basketball',600.00,'case','24/case');
INSERT INTO stock
VALUES (8,'ANZ','volleyball',840.00,'case','24/case');
INSERT INTO stock
VALUES (9,'ANZ','volleyball net',20.00,'each','each');
CREATE TABLE items
(item_num NUMBER(2),
order_num NUMBER(4),
stock_num NUMBER(2),
manu_code CHAR(3),
quantity NUMBER(3),
total_price NUMBER(8,2),
PRIMARY KEY (item_num,order_num),
FOREIGN KEY (stock_num, manu_code)
REFERENCES stock (stock_num, manu_code))
INSERT INTO items
VALUES (1,1001,1,'HRO',1,250.00);
INSERT INTO items
VALUES (1,1002,4,'HSK',1,960.00);
INSERT INTO items
VALUES (2,1002,3,'HSK',1,240.00);
INSERT INTO items
VALUES (1,1003,9,'ANZ',1,20.00);
INSERT INTO items
VALUES (2,1003,8,'ANZ',1,840.00);
INSERT INTO items
VALUES (3,1003,5,'ANZ',5,99.00);
INSERT INTO items
VALUES (1,1004,1,'HRO',1,960.00);
INSERT INTO items
VALUES (2,1004,2,'HRO',1,126.00);
INSERT INTO items
VALUES (3,1004,3,'HSK',1,240.00);
INSERT INTO items
VALUES (4,1004,1,'HSK',1,800.00);
INSERT INTO items
VALUES (1,1005,5,'NRG',10,280.00);
INSERT INTO items
VALUES (2,1005,5,'ANZ',10,198.00);
INSERT INTO items
VALUES (3,1005,6,'SMT',1,36.00);
INSERT INTO items
VALUES (4,1005,6,'ANZ',1,48.00);
INSERT INTO items
VALUES (1,1006,5,'SMT',5,125.00);
INSERT INTO items
VALUES (2,1006,5,'NRG',5,190.00);
INSERT INTO items
VALUES (3,1006,5,'ANZ',5,99.00);
INSERT INTO items
VALUES (4,1006,6,'SMT',1,36.00);
INSERT INTO items
VALUES (5,1006,6,'ANZ',1,48.00);
INSERT INTO items
VALUES (1,1007,1,'HRO',1,250.00);
INSERT INTO items
VALUES (2,1007,2,'HRO',1,126.00);
INSERT INTO items
VALUES (3,1007,3,'HSK',1,240.00);
INSERT INTO items
VALUES (4,1007,4,'HRO',1,480.00);
INSERT INTO items
VALUES (5,1007,7,'HRO',1,600.00);
INSERT INTO items
VALUES (1,1008,8,'ANZ',1,840.00);
INSERT INTO items
VALUES (2,1008,9,'ANZ',5,100.00);
INSERT INTO items
VALUES (1,1009,1,'SMT',1,450.00);
INSERT INTO items
VALUES (1,1010,6,'SMT',1,36.00);
INSERT INTO items
VALUES (2,1010,6,'ANZ',1,48.00);
INSERT INTO items
VALUES (1,1011,5,'ANZ',5,99.00);
INSERT INTO items
VALUES (1,1012,8,'ANZ',1,840.00);
INSERT INTO items
VALUES (2,1012,9,'ANZ',10,200.00);
INSERT INTO items
VALUES (1,1013,5,'ANZ',1,19.80);
INSERT INTO items
VALUES (2,1013,6,'SMT',1,36.00);
INSERT INTO items
VALUES (3,1013,6,'ANZ',1,48.00);
INSERT INTO items
VALUES (4,1013,9,'ANZ',2,40.00);
INSERT INTO items
VALUES (1,1014,4,'HSK',1,960.00);
INSERT INTO items
VALUES (2,1014,4,'HRO',1,480.00);
INSERT INTO items
VALUES (1,1015,1,'SMT',1,450.00);
CREATE TABLE customer
(customer_num NUMBER(3) CONSTRAINT customer_num_pk PRIMARY KEY,
fname VARCHAR2(15),
lname VARCHAR2(15),
company VARCHAR2(20),
address1 VARCHAR2(20),
address2 VARCHAR2(20),
city VARCHAR2(15),
state CHAR(2),
zipcode CHAR(5),
phone VARCHAR(18)
)
INSERT INTO customer
VALUES (101,'Ludwig','Pauli','All Sports Supplies','213 Erstwild Court',
NULL,'Sunnyvale','CA','94086','408-789-8075');
INSERT INTO customer
VALUES (102,'Carole','Sadler','Sports Spots','785 Geary St',
NULL,'San Francisco','CA','94117','415-822-1289');
INSERT INTO customer
VALUES (103,'Phillip','Currie','Phil''s Sports','654 Poplar',
'P.O. Box 3498','Palo Alto','CA','94303','415-328-4543');
INSERT INTO customer
VALUES (104,'Anthony','Higgins','Play Ball!','East Shopping Cntr.',
'422 Bay Road','Redwood City','CA','94026','415-368-1100');
INSERT INTO customer
VALUES (105,'Raymond','Vector','Los Altos Sports','1899 Le Loma Drive',
NULL,'Los Altos','CA','94022','415-776-3249');
INSERT INTO customer
VALUES (106,'George','Watson','Watson and Son','1143 Carver Place',
NULL,'Mountain View','CA','94063','415-389-8789');
INSERT INTO customer
VALUES (107,'Charles','Ream','Athletic Supplies','41 Jordan Avenue',
NULL,'Palo Alto','CA','94304','415-356-9876');
INSERT INTO customer
VALUES (108,'Donald','Quinn','Quinn''s Sports','587 Alvarado',
NULL,'Redwood City','CA','94063','415-544-8729');
INSERT INTO customer
VALUES (109,'Jane','Miller','Sport Stuff','Mayfair Mart',
'7345 Ross Blvd.','Sunnyvale','CA','94086','408-723-8789');
INSERT INTO customer
VALUES (110,'Roy','Jaeger','AA athletics','520 Topaz Way',
NULL,'Redwood City','CA','94062','415-743-3611');
INSERT INTO customer
VALUES (111,'Frances','Keyes','Sports Center','3199 Sterling Court',
NULL,'Sunnyvale','CA','94085','408-277-7245');
INSERT INTO customer
VALUES (112,'Margaret','Lawson','Runners and Others','234 Wyandotte Way',
NULL,'Los Altos Hills','CA','94022','415-887-7235');
INSERT INTO customer
VALUES (113,'Lana','Beatty','Sportstown','654 Oak Grove',
NULL,'Menlo Park','CA','94025','415-356-9982');
INSERT INTO customer
VALUES (114,'Frank','Albertson','Sporting Place','947 Waverly Place',
NULL,'Redwood City','CA','94062','415-886-6677');
INSERT INTO customer
VALUES (115,'Alfred','Grant','Gold Medal Sports','776 Gary Avenue',
NULL,'Menlo Park','CA','94025','415-356-1123');
INSERT INTO customer
VALUES (116,'Jean','Parmelee','Olympic City','1104 Spinosa Drive',
NULL,'Mountain View','CA','94040','415-534-8822');
INSERT INTO customer
VALUES (117,'Arnold','Sipes','Kids Korner','850 Lytton Court',
NULL,'Redwood City','CA','94063','415-245-4578');
INSERT INTO customer
VALUES (118,'Dick','Baxter','Blue Ribbon Sports','5427 College',
NULL,'Oakland','CA','94609','415-655-0011');
CREATE TABLE orders
(order_num NUMBER(4) CONSTRAINT order_num_pk PRIMARY KEY,
order_date DATE CONSTRAINT order_date_nn NOT NULL,
customer_num NUMBER(3) CONSTRAINT order_cust_num_fk
REFERENCES CUSTOMER(customer_num),
ship_instruct VARCHAR2(40),
backlog CHAR(1),
po_num VARCHAR(10),
ship_date DATE,
ship_weight NUMBER(8,2),
ship_charge NUMBER(6,2),
paid_date DATE)
INSERT INTO orders
VALUES (1001,TO_DATE('12/30/1999','MM/DD/YYYY'),104,
'ups','n','B77836',
TO_DATE('09/31/1999','MM/DD/YYYY'),20.40,10.00,
TO_DATE('01/12/2000','MM/DD/YYYY'));
INSERT INTO orders
VALUES (1002,TO_DATE('12/20/1999','MM/DD/YYYY'),101,
'po on box; deliver back door only','n','9270',
TO_DATE('09/28/1999','MM/DD/YYYY'),50.60,15.30,
TO_DATE('01/03/2000','MM/DD/YYYY'));
INSERT INTO orders
VALUES (1003,TO_DATE('12/05/1999','MM/DD/YYYY'),104,'via ups','n','B77890',
TO_DATE('12/08/1999','MM/DD/YYYY'),35.60,10.80,
TO_DATE('12/14/1999','MM/DD/YYYY'));
INSERT INTO orders
VALUES (1004,TO_DATE('12/30/1999','MM/DD/YYYY'),106,
'ring bell twice','y','8006',
NULL,95.80,19.20,
NULL);
INSERT INTO orders
VALUES (1005,TO_DATE('12/30/1999','MM/DD/YYYY'),116,
'call before delivering','n','2865',
TO_DATE('12/03/2000','MM/DD/YYYY'),80.80,16.20,
TO_DATE('12/10/2000','MM/DD/YYYY'));
INSERT INTO orders
VALUES (1006,TO_DATE('12/30/1999','MM/DD/YYYY'),112,
'after 10 am','y','Q13557',
NULL,70.80,14.20,
NULL);
INSERT INTO orders
VALUES (1007,TO_DATE('12/30/1999','MM/DD/YYYY'),117,NULL,'n','278693',
TO_DATE('01/03/2000','MM/DD/YYYY'),125.90,25.20,
NULL);
INSERT INTO orders
VALUES (1008,TO_DATE('12/30/1999','MM/DD/YYYY'),110,
'closed Monday','y','LZ230',
TO_DATE('01/15/2000','MM/DD/YYYY'),45.60,13.80,
TO_DATE('01/22/2000','MM/DD/YYYY'));
INSERT INTO orders
VALUES (1009,TO_DATE('12/01/1999','MM/DD/YYYY'),111,
'door next to supersaver','n','4745',
TO_DATE('12/12/1999','MM/DD/YYYY'),20.40,10.00,
TO_DATE('12/15/1999','MM/DD/YYYY'));
INSERT INTO orders
VALUES (1010,TO_DATE('12/31/1999','MM/DD/YYYY'),115,
'deliver 776 Gary if no answer','n','429Q',
TO_DATE('01/02/2000','MM/DD/YYYY'),40.60,12.30,
TO_DATE('01/22/2000','MM/DD/YYYY'));
INSERT INTO orders
VALUES (1011,TO_DATE('12/31/1999','MM/DD/YYYY'),104,'ups','n','B77897',
TO_DATE('01/02/2000','MM/DD/YYYY'),10.40,5.00,
TO_DATE('01/13/2000','MM/DD/YYYY'));
INSERT INTO orders
VALUES (1012,TO_DATE('12/31/1999','MM/DD/YYYY'),117,NULL,'n','278701',
TO_DATE('01/05/2000','MM/DD/YYYY'),70.80,14.20,
NULL);
INSERT INTO orders
VALUES (1013,TO_DATE('01/03/2000','MM/DD/YYYY'),104,'via ups','n','B77930',
TO_DATE('01/08/2000','MM/DD/YYYY'),60.80,12.20,
TO_DATE('01/17/2000','MM/DD/YYYY'));
INSERT INTO orders
VALUES (1014,TO_DATE('12/31/1999','MM/DD/YYYY'),106,
'ring bell kick door loudly','n','8052',
TO_DATE('01/04/2000','MM/DD/YYYY'),40.60,12.30,
TO_DATE('01/16/2000','MM/DD/YYYY'));
INSERT INTO orders
VALUES (1015,TO_DATE('01/03/2000','MM/DD/YYYY'),110,'closed Mon','n','MA003',
TO_DATE('01/08/2000','MM/DD/YYYY'),20.60,6.30,
TO_DATE('01/24/2000','MM/DD/YYYY'));
Explanation / Answer
Try this. Notice the bold part in the query.
Explanation- GROUP BY clause can not refer to non aggregated columns in SELECT that are not there in GROUP BY clause. The given query will be illegal if all non aggregated columnsin SELECT are not mention in GROUP BY.
SELECT C.company, S.description, M.manu_name, SUM(I.quantity), SUM(I.quantity*I.total_price)
FROM customer C, orders O, items I, stock S, manufact M
WHERE C.customer_num=O.customer_num AND O.order_num=I.order_num
AND S.manu_code=M.manu_code
GROUP BY C.company, S.description, M.manu_name
ORDER BY S.description;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.