Untitled

Find total number of customers having at least 5 orders (COUNT with JOIN, GROUP BY and HAVING)

SELECT COUNT(*) FROM (

 

  SELECT c.id FROM customers as c

  INNER JOIN orders as o ON c.id = o.customer_id

  GROUP BY c.id

  HAVING COUNT(o.id) >= 5

 

) as temp

       

total_count = Customer.joins("INNER JOIN orders ON customers.id = orders.customer_id").group("customers.id").having("COUNT(orders.id) >= 5").count

       

SELECT COUNT(*) AS count_all, customers.id AS customers_id FROM `customers` INNER JOIN orders ON customers.id = orders.customer_id GROUP BY customers.id HAVING COUNT(orders.id) >= 5

       

SELECT * FROM orders

GROUP BY customer_id

HAVING COUNT(customer_id) >= 5