How to make my WHERE clause not run a syntax error in SQL?

The questions asks,

“Write a query to display the customer name and the number of payments they have made where the amount on the check is greater than their average payment amount. Order the results by the descending number of payments.”

So far I have,

 SELECT customerName, 
                (SELECT COUNT(checkNumber) FROM Payments WHERE 
                 Customers.customerNumber = Payments.customerNumber) AS
                 NumberOfPayments
FROM Customers
WHERE amount > SELECT AVG(amount)
ORDER BY NumberOfPayments DESC; 

But I am getting a syntax error every-time I run out. What am I doing incorrectly in this situation?

Answer

The syntax error comes from the fact that you are having an incorrect second subquery: amount > SELECT AVG(amount) doesn’t work.

You could use amount > (SELECT AVG(amount) FROM Payments).

That is: complete the subquery and put it between ( ).

However this won’t do what you want (plus it is inefficient).

Now since this is not a forum to do your homework for you, I will leave it at this and thus only help you with the actual question: why do you get the syntax error. Keep on looking, you will find it. No better way to learn than to search and find yourself.