Hello,

I need help with a query.
I have 2 tables.
- table1: Customer ID - Customer name
- table 2: Customer ID - Orders

I would like to run a query that reports for each Customer the total number of orders made.

Customer name Orders Count
Jhon Smith 10
Jhon Doe 22

Thanks

Join these tables, GROUP BY customer ID.

CREATE TABLE CUSTOMER(CUSTOMER_ID NUMBER , CUSTOMER_NAME VARCHAR2(20));

INSERT INTO CUSTOMER (CUSTOMER_ID,CUSTOMER_NAME) VALUES (1,'Jhon Smith ');
INSERT INTO CUSTOMER (CUSTOMER_ID,CUSTOMER_NAME) VALUES (2,'Jhon Doe');



CREATE TABLE CUSTOMER_ORDER(CUSTOMER_ID NUMBER , ORDER_ID VARCHAR2(20));

INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (1,'50');
Insert into CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) values (2,'51');
INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (1,'25');
INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (2,'28');
INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (1,'26');
INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (2,'52');
INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (1,'66');
INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (2,'47');
INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (1,'32');
INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (1,'8');
INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (1,'5');
INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (1,'7');
INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (2,'7');
INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID,ORDER_ID) VALUES (2,'8');



SELECT CUSTOMER_ID, CUSTOMER_NAME FROM CUSTOMER ;

CUSTOMER_ID CUSTOMER_NAME
---------------------- --------------------
1 JHON SMITH
2 Jhon Doe

SELECT CUSTOMER_ID, ORDER_ID FROM CUSTOMER_ORDER ;

CUSTOMER_ID ORDER_ID
---------------------- --------------------
1 50
2 51
1 25
2 28
1 26
2 52
1 66
2 47
1 32
1 8
1 5
1 7
2 7
2 8

14 rows selected


SELECT CUS.CUSTOMER_ID, CUS.CUSTOMER_NAME, COUNT(*) TOTAL_ORDERS FROM CUSTOMER CUS
INNER JOIN CUSTOMER_ORDER ORD
ON CUS.CUSTOMER_ID = ORD.CUSTOMER_ID
GROUP BY CUS.CUSTOMER_ID ,CUS.CUSTOMER_NAME ;



CUSTOMER_ID CUSTOMER_NAME TOTAL_ORDERS
---------------------- -------------------- ----------------------
2 Jhon Doe 6
1 JHON SMITH 8

learning on schema hr
are you two in the same class?

Thanks you folks for your help and detailed answer.