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

foreign keyoracleoracle-11g

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!

Best Answer

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 ;

Related Solutions

Sqlplus not connecting to Oracle XE 11g

Cannot allocate memory means you have not enough ram. check the memory requirements in the oralce guide

Not exists in sql query

First of all, NOT IN is not the same as NOT EXISTS.

create table customer (customerid number);
create table purchasebill (customerid number, retailoutletid number);
create table retailoutlet (retailoutletid number);

insert into customer values (1);
insert into customer values (2);

insert into purchasebill values (1, 1);
insert into purchasebill values (null, 3);

insert into retailoutlet values (1);
insert into retailoutlet values (3);

commit;

Let's find customers without purchasebill (customerid = 2). FIrst with a naive NOT IN:

SQL> select * from customer c where c.customerid not in (select p.customerid from purchasebill p);

no rows selected

Now with NOT EXISTS:

SQL> select * from customer c where not exists (select 1 from purchasebill p where p.customerid = c.customerid);

CUSTOMERID
----------
         2

NOT IN did not provide the result we expected. It's because the way NOT IN works. If any row of the subquery returns NULL, NOT IN will return FALSE, so no result in this case.

Now your queries:

SQL> select * from customer c where not exists (select p.customerid from customer c, purchasebill p, retailoutlet r where p.customerid = c.customerid and r.retailoutletid = p.retailoutletid);

no rows selected

SQL> select * from customer c where customerid not in (select p.customerid from customer c, purchasebill p, retailoutlet r where p.customerid = c.customerid and r.retailoutletid = p.retailoutletid);

CUSTOMERID
----------
         2

The problem with the NOT EXISTS query is that you have customer c both in the outer query and the subquery. The subquery uses customer c from its inside, not the customer c from the outer query. Because of this, the subquery always returns data, so NOT EXISTS will always evaluate to FALSE, hence no result.

The NOT IN version works, because the inner query is not a correlated subquery, it does not use customer c from the outside, but the inside. The inner query returns customers who made purchases, without NULLs, because the join to purchasebill p eliminates them. With no NULLs to worry anymore, NOT IN works as naively expected.

If you want to make NOT EXISTS work, remove customer c from the subquery, so it becomes a correlated subquery:

SQL> select * from customer c where not exists (select 1 from purchasebill p, retailoutlet r where p.customerid = c.customerid and r.retailoutletid = p.retailoutletid);

CUSTOMERID
----------
         2

Best Answer

Related Solutions

Sqlplus not connecting to Oracle XE 11g

Not exists in sql query

Related Question