AidTech is 2D sci-fi action adventure with focus on the story. Main elements of this game are your decisions - with them you influence the game. So your decisions will matter. You use teleportation, laser weapons and energetic shields to your advantage. You will come through research complexes, space ships, and remains of civilization scattered in the universe to save what is most important for you. Read more about AidTech

Select customers and count of orders which aren’t canceled SQL


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?