Why is the AVG not returning anything in my Where clause in SQL?

The question ask,

“List the customer name and phone number for customers whose credit limit is less than the average credit limit. (Customers table)”

So far I have

SELECT customerName, phone
FROM Customers 
WHERE creditLimit < (SELECT AVG (creditLimit)
                           FROM Products); 

Yet this code is not returning anything, what am I doing wrong? Thank you.

Answer

I would be surprised if Products has a credit limit. If you qualify your column names, you will immediately see your error:

SELECT c.customerName, c.phone
FROM Customers c
WHERE c.creditLimit < (SELECT AVG(p.creditLimit)
                       FROM Products p
                      ); 

In other words, you should be using Customers in the subquery. What is happening is that the query is interpreted like this:

SELECT c.customerName, c.phone
FROM Customers c
WHERE c.creditLimit < (SELECT AVG(c.creditLimit)
                       FROM Products p
                      ); 

That is, the subquery is using the creditLimit from the row being compared, rather than from the table referenced in the subquery (because there is no such column). A value is never less than itself, so this never evaluates to true.

A more important lesson to learn: always qualify your column names, so you don’t ever have a problem like this again.