2

I am in school to become a software engineer, and I am taking my first class on SQL. We usually use MsSQL, but the schools VMWare instance is down, so I have an older version of Oracle on my computer (Oracle 11g Express). I am trying to take my customer table, and orderInfo table to get the count of each customers # of orders. I have it working if I use the orderTable, but then it would not get customers that have 0 orders. Basically, if the customer is not in the orderInfo table, they won't have an order count. Please See the table structure below and my SQL code.

Customer Table Customer Table Order Info Table OrderInfo Table

This is my current SQL code and the output.

SELECT CUSTOMER_ID, COUNT(ORDERINFO.ORDER_ID) AS TotalOrders
FROM ORDERINFO
WHERE CUSTOMER_ID = CUSTOMER_ID
GROUP BY CUSTOMER_ID;

Output: Output of SQL code

Thank you so much for the help in advance!

1

A join of some type between the two tables is needed - and then group by customers.customer_id. Then to get results for customers with no orders, one way would be to use a LEFT join:

SELECT 
    C.CUSTOMER_ID, 
    COUNT(O.ORDER_ID) AS TotalOrders
FROM 
        CUSTOMERS  C 
    LEFT JOIN 
        ORDERINFO  O
    ON 
        O.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY 
    C.CUSTOMER_ID ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.