Show how many orders each customer has made and the total for all the the custom
ID: 356354 • Letter: S
Question
Show how many orders each customer has made and the total
for all the the customer's orders.
Show the sum of a customer's orders
the order count, customer ID,
and last name.
Use a column alias for the order count and total.
Sort by the sum of orders in descending order and then by last name.
*/
OE ORDER ITEMS OE.ORDERS PF ORDER ID NUMBER (12) NUMBER (12) TIMESTAMP WITH LOCAL TIME ZONE P LINE_ITEM_ID NUMBER (3) PRODUCT ID NUMBER(8) ORDER_DATE ORDER MODE VARCHAR2 (8 BYTE) CUSTOMER ID ORDER_STATUS NUMBER (2) ORDER TOTAL NUMBER (8,2) NUMBER (8,2) UNIT PRICE QUANTITY NUMBER (6 ORDER_ITEMS_PK (ORDER_ID, LINE_ITEMID) ORDER_ITEMS_ORDER_ID_FK (ORDER ID) ?ORDER-ITEMS-PRODUCT-ID-FK (PRODUCT-ID) ? ORDER-ITEMS-PK (ORDER-ID, LINE-ITEM-ID) PROMOTIONLID NUMBER (8) ORDER_PK (ORDER_ID) FK CUSTID (CUSTOMER ID) ORDER-PK (ORDER-ID) ? OE PRODUCT_INFORMATION P PRODUCT_ID NUMBER (8) VARCHAR2 (50 BYTE VARCHAR2 (2000 BYTE) NUMBER (2) NUMBER (1) INTERVAL YEAR TO MONTH NUMBER (8) VARCHAR2 (20 BYTE) NUMBER (8.2) NUMBER (8.2) VARCHAR2 (50 BYTE) OE.CUSTOMERS PRODUCT NAME PRODUCT_DESCRIPTION CATEGORY_ID WEIGHT CLASS WARRANTY PERIOD SUPPLIER ID PRODUCT STATUS CUSTOMER ID NUMBER (8) VARCHAR2 (20 BYTE) VARCHAR2 (20 BYTE) UNKNOWN UNKNOWN VARCHAR2 (3 BYTE) VARCHAR2 (30 BYTE) NUMBER (9,2) VARCHAR2 (30 BYTE) NUMBER (8) · CUSTLASTNAME CUST ADDRESS PHONE NUMBERS NLS LANGUAGE NLS TERRITORY - - IN PRICE CATALOG URL PRODUCT_INFORMATION_PK (PRODUCT_ID) PRODUCT-INFORMATION-PK (PRODUCT-ID) CUST EMAIL ACCOUNT MGRID CUST GEO LOCATION SDO GEOMETRY DATE OF BIRTH MARITALSTATUS GENDER INCOMELEVEL ? VARCHAR2 (20 BYTE) VARCHAR2 (1 BYTE) VARCHAR2 (20 BYTE) CUSTOMERS_PK (CUSTOMER ID) CUSTOMERS_PK (CUSTOMER ID) OE.INVENTORIES OE PRODUCT DESCRIPTIONS PF* PRODUCT ID PF* PRODUCT ID NUMBER (8) NUMBER (3) NUMBER (8) VARCHAR2 (3 BYTE) NVARCHAR2 (50) F WAREHOUSE ID · LANGUAGE ID QUANTITY_ON HAND NUMBER (8) INVENTORY_PK (PRODUCT_ID, WAREHOUSE_ID) INVENTORIES-PRODUCT-ID-FK (PRODUCT_ID) INVENTORIES_WAREHOUSES_FK(WAREHOUSE_ID) INVENTORY-PK (PRODUCT-ID. TRANSLATED_NAME TRANSLATED DESCRIPTION NVARCHAR2 (2000) PRODUCT-DESCRIPTIONS-PK (PRODUCT-ID?LANGUAGE-ID) PD_PRODUCT_ID_FK (PRODUCT_ID) PRODUCT-DESCRIPTIONS-PK (PRODUCT-D.LANGUAGE..ID) ? WAREHOUSE-ID) ? OE WAREHOUSES WAREHOUSE_ID WAREHOUSE SPEC XMLTYPE WAREHOUSE_NAME VARCHAR2 (35 BYTE) LOCATIONID WH GEO LOCATION SDO_GEOMETRY NUMBER (3) NUMBER WAREHOUSES_PK (WAREHOUSE_ID) ? WAREHOUSES-PK(WAREHOUSE-D)Explanation / Answer
Show how many orders each customer has made and the total
for all the the customer's orders.
select count (ORDER_ID), ORDER_TOTAL from OE.ORDERS GROUP BY CUSTOMER_ID
Show the sum of a customer's orders
the order count, customer ID, and last name.
Select count (A.ORDER_ID), A.CUSTOMER_ID, B.CUST_LAST_NAME from OE.ORDERS A AND OE.CUSTOMERS B where A.CUSTOMER_ID = B.CUSTOMER_ID GROUP BY CUSTOMER_ID ORDER BY count (A.ORDER_ID) desc, B.CUST_LAST_NAME