Aggregates and GROUP BY

SQL has five core aggregate functions: COUNT (counts rows), SUM (adds up values), AVG (computes the mean), MIN (finds the smallest value), and MAX (finds the largest). Each of these collapses many rows into a single number. When you add GROUP BY, SQL first splits the rows into groups — one group per unique value of the grouping column — and then applies the aggregate to each group separately. The result table has exactly one row per group. Consider an orders table with columns city and amount: city | amount -------+------- London | 50 London | 70 Paris | 40 Paris | 20 Paris | 30 The query SELECT city, COUNT(*), SUM(amount), AVG(amount), MIN(amount), MAX(amount) FROM orders GROUP BY city produces: city | COUNT | SUM | AVG | MIN | MAX -------+-------+-----+-----+-----+----- London | 2 | 120 | 60 | 50 | 70 Paris | 3 | 90 | 30 | 20 | 40 One rule you must remember: every column in the SELECT list that is not inside an aggregate function must appear in the GROUP BY clause. Otherwise, SQL does not know which value to show for that column.