0

I have these 3 tables with the following structures:

Products

product_id | category_id | product_name

Customers

customer_id | customer_name

Orders

order_id | customer_id | product_id

I need to write a SQL query that will print the top customers regarding the number of categories they have bought products from (the best customer is the one that has bought from the most categories).

Can anyone show me how to do that?

I tried like this, but I get the following error "not a GROUP BY expression":

select 
    (select customer_name 
     from customers 
     where customers.customer_id = orders.customer_id) as name,    
    (select count(category_id) 
     from products 
     where products.product_id = orders.product_id)
from 
    orders
group by 
    customer_id
order by 
    count(customer_id) desc;

I managed to make the top regarding how many products the customers bought. The solution I used is:

select 
    (select customer_name from customers 
     where customers.customer_id = orders.customer_id) as name,   
    count(product_id) as "number of ordered products"
from 
    orders
group by 
    customer_id
order by 
    count(product_id) desc;
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Athena
  • 1

1 Answers1

0

Nested queries for this? Sheesh...
Just join them already.
And then order by the unique categories

SELECT c.customer_name
, COUNT(DISTINCT p.category_id) AS TotalOrderedCategories
FROM Orders o
LEFT JOIN Customers c ON c.customer_id = o.customer_id
LEFT JOIN Products p ON p.product_id = o.product_id
GROUP BY c.customer_name
ORDER BY COUNT(DISTINCT p.category_id) DESC

Then depending on your RDBMS flavor, add a TOP or a LIMIT.

LukStorms
  • 28,916
  • 5
  • 31
  • 45