COUNT, SUM, AVG, MIN, and MAX

An aggregate returns one value per group, and without GROUP BY the whole result is one group. Revenue per month from March to May, then the dearest title per category:

Revenue per month, then a column that is neither grouped nor aggregatedSQL

SELECT DATE_FORMAT(o.ordered_at, '%Y-%m') AS month, COUNT(DISTINCT o.id) AS orders,
       COUNT(*) AS line_items, SUM(i.quantity * i.unit_price) AS revenue,
       ROUND(AVG(i.unit_price), 2) AS avg, MIN(i.unit_price) AS min, MAX(i.unit_price) AS max
FROM orders AS o JOIN order_items AS i ON i.order_id = o.id
WHERE o.status <> 'cancelled' AND o.ordered_at >= '2026-03-01' AND o.ordered_at < '2026-06-01'
GROUP BY month ORDER BY month;
SELECT category_id, title, MAX(price) FROM products GROUP BY category_id;

Output

+---------+--------+------------+---------+-------+-------+-------+
| month   | orders | line_items | revenue | avg   | min   | max   |
+---------+--------+------------+---------+-------+-------+-------+
| 2026-03 |      2 |          5 |  153.37 | 26.18 |  4.99 | 44.50 |
| 2026-04 |      1 |          1 |   49.00 | 49.00 | 49.00 | 49.00 |
| 2026-05 |      1 |          2 |   86.50 | 43.25 | 42.00 | 44.50 |
+---------+--------+------------+---------+-------+-------+-------+
3 rows in set (0.001 sec)
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains
  nonaggregated column 'shop.products.title' which is not functionally dependent on columns in
    GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

COUNT(*) counts joined lines and COUNT(DISTINCT o.id) orders. Other aggregates skip NULL (COUNT(body) over reviews is 6 of 7), and on an empty set all but COUNT return NULL, so write COALESCE(SUM(x), 0). The error protects you: servers before 5.7.5 paired MAX(price) with a title from an arbitrary row. ANY_VALUE(title) hides that; a ranking function (Ranking Functions) fixes it.

MENU

COUNT, SUM, AVG, MIN, and MAX