When I was doing some SQL filtration over customers, I needed to sort them with count of their orders. OK, that is easy, just use left join:
SELECT customers.ID, COUNT(*) AS orders FROM customers LEFT JOIN orders ON customers.ID=orders.customer_ID WHERE orders.status NOT LIKE 'canceled' AND customers.business_ID=10 GROUP BY customers.ID
Not really. The problem with my SQL was, that it only showed customers which had some order, but not those who hadn’t. Problem was with this line:
orders.status NOT LIKE 'canceled'
This WHERE condition totally ruined my SQL, because I needed to count only orders which weren’t canceled. Hopefully my friend helped me out:
SELECT customers.ID, count(orders.ID) - sum(if(orders.status = 'canceled',1,0)) as pocet FROM customers LEFT JOIN orders ON customers.ID=orders.customer_ID GROUP BY customers.ID
This SQL if statement will subtract number of canceled orders from the total count of orders. Simple, huh?