How to Count Rows in a Separate Table Using a Foreign Key

Question

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 Order Info 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:

Thank you so much for the help in advance!

Answer 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 ;