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...