Database Normalization at Fred's Furniture step 18 help

Hello,

I am currently working on this project in the database normalization module. Here is the link to the project: Link

I have a question about step 18 where it poses the problem: “How many customers made more than one order?”

I tried to solve this problem by writing the sql query:

SELECT customers.customer_id, COUNT(*) as tot_orders
FROM customers
JOIN orders
  ON customers.customer_id = orders.customer_id
  WHERE tot_orders > 1
GROUP BY customers.customer_id;

However, the query doesn’t work because of the line WHERE tot_orders > 1. I get this error:

I don’t understand why I am getting this error. Is there a way to filter the query so that I only show the customers that have more than 1 order? Any help is greatly appreciated. Thanks everyone and happy coding :slight_smile:

Hello.

You can’t use result of aggregation function (COUNT, MIN, MAX, AVG, etc.) in WHERE clause. You can use HAVING (PostgreSQL: Documentation: 14: 7.2. Table Expressions) or subqueries (PostgreSQL: Documentation: 14: 7.2. Table Expressions).

And small advice: do not use * as COUNT argument, use name of the field which you count, order_id in this case because you count orders. It will work absolutely the same, but query will be more easier to understand.

Hope I could help.

2 Likes

@9509706156 , Thanks for your help! This really helped me a lot and I figured it out. I forgot about the HAVING query.

Also thanks for the advice about being more specific with my queries. All the best. Happy coding :slight_smile:

1 Like

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.