MySql Query - Number of orders per customer

[Andy_nexus]

Hey guys im trying to do a query on our database, the pieces of information i need are in 2 tables within my database.

table 1= customers
- information i need from this table:
customers_id (primary)
customers_firstname
customers_lastname
customers_email_address

table 2= orders
customers_id
orders_id (primary)

both tables contain more fields than just the above stated.

I'm trying to find out which customer has made the most orders, my code below works fine on a small database:

SELECT customers_firstname, customers_lastname,
COUNT(orders_id) AS Number
FROM customers LEFT JOIN orders AS o
USING (customers_id) GROUP BY (o.customers_id)
ORDER BY Number DESC LIMIT 5;

However there's over 35000 people in the database so the query always crashes.

the problem as i see it is with the LEFT JOIN, because as far as i understand it the left join will basically merge the two tables with all fields.

im still a rather new to mysql databases can anyone help?

regards

Andy.

[cgyrob]

The first thing you need to do is replace the left join with an Inner join. Right now you are joining all the customers even if they do not have any orders which is a waste of resources when you are looking for the customers that rank in the top5 of orders.

If that alone does not solve your problem you can try using a derived table to calculate the top 5 customers.

SELECT customers_firstname, customers_lastname,o.Number
FROM customers 
INNER JOIN (Select customer_id, count(*) as Number from orders Group by customer_id order by Number desc limit 5)AS o
on customers.customers_id = o.customer_id ;

Let me know if this helps.

[Andy_nexus]

Hey yeh the inner join did the trick.
Think because our servers are running mysql 4 the syntax you provided didn't work.

But i simply changed the Left join to Inner join and it worked great.

thanks

again.

[cgyrob]

Glad I could help. Can you mark the thread solved if your problem has been fixed.

Thanks.