Aaron Johnson Now with 50% less caffeine!

SQL: getting the count of the result of a derived table

Recording this for posterity: let’s say you’re working on a store and you’ve got a database table that stores orders and a database table that stores customers and that you want to get a count of all the customers who’ve ordered more than 10 times. You’re going to write a query that looks like this:

SELECT customerID FROM orders GROUP BY customerID HAVING COUNT(*) > 10

to get a list of all the customerID’s who’ve ordered more than ten times and a query like this:

SELECT COUNT(*) FROM (SELECT customerID FROM orders GROUP BY customerID HAVING COUNT(*) > 10) as n

to get a count of those customers. Just sayin’.