Aggregate Functions with the GROUP BY Clause

So far, you have used aggregate functions to calculate statistics for an entire column. However, most times you are interested in not only the aggregate values for a whole table but also the values for smaller groups in the table. To illustrate this, refer back to the customers table. You know that the total number of customers is 50,000. However, you might want to know how many customers there are in each state. But how can you calculate this?

You could determine how many states there are with the following query:

SELECT DISTINCT 
  state 
FROM 
  customers;

You will see 50 distinct states, Washington D.C., and NULL returned as a result of the preceding query, totaling 52 rows. Once you have the list of states, you could then run the following query for each state:

SELECT 
  COUNT(*) 
FROM 
  customers 
WHERE 
  state='{state}'

Although you can do this, it is...