1

I have a query. I just would like to get the number of orders for each distinct customer. But the results give me very high numbers, most probably somehow summing up all ids. What am I missing?

SELECT
  COUNT(b.order_id) AS number_of_orders_for_each_distinct_customer,
  a.customer_id
FROM messages a
  INNER JOIN orders b ON a.customer_id = b.customer_id
  INNER JOIN order_items c ON c.order_id = b.order_id
  INNER JOIN products d ON c.product_id = c.product_id
GROUP BY a.customer_id
2

By JOINing to order_items that query returns one line for each row in the order_items table. The COUNT is therefore counting rows from order_items, not from table orders. This is why you think you are over counting.

Solutions would be to remove the joins to tables which are not referenced directly in the resultset, or to use the SELECT COUNT(DISTINCT ..) syntax.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.