I consider myself a rather proficient mysql user, having used it for rather complicated queries over the years. But on this particular ocassion, after several days trying to figure it out, there has to be something that still escapes me on all of this. Lets start with the query:
What I am trying to do here is to show a row for every customer in the database that shows the number of orders placed per customer (a single customer being defined as all those orders placed with the same email address, hence the GROUP BY), but I am running into the issue that whenever I join the customer_orders_detail table (line items for every order, if you will) to the query, the count returns every single row where there is a matching line item, therefore giving me a count of the total number of line items a customer has purchased, rather than actual number of orders. Thus, if a customer has placed 1 order with 1 line item and another one with 2, the total count is displayed as 3 when it should be 2. The customer_orders_detail table has to be present in the query since there is a filtering on this report that allows the user to restrict the results to certain products purchased, otherwise the query would work like a charm.
customer_orders.customer_order_id AS 'order_id',
customer_orders.address_book_id AS 'address_id',
customer_orders_detail.customer_order_detail_id AS 'order_detail_id',
customer_orders_detail.customer_order_id AS 'order_id',
customer_orders_detail.product_id AS 'product_id',
address_book.address_book_id AS 'address_id', address_book.email AS 'email',
COUNT( customer_orders.customer_order_id ) AS num_orders
LEFT JOIN customer_orders_detail
USING ( customer_order_id )
LEFT JOIN address_book
ON customer_orders.address_book_id = address_book.address_book_id
GROUP BY address_book.email ASC
Do you guys have any ideas as to how to tackle this particular issue?
Thank you very much, I hope I was clear in the description.