Have tried the following, but error for not a single-group group function on lin

ID: 3602984 • 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

Navigate

Have to write a program in Java that sums the following summation: from 1 / (1+S

Have tried the following, but error for not a single-group group function on lin

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Have tried the following, but error for not a single-group group function on lin

Question

Explanation / Answer

Related Questions

Navigate