COUNT, SUM, AVG, MIN and MAX
40 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)An aggregate function reads one column from many rows and gives back a single value: how many rows there are, their total, their average, the smallest or the largest. A fee total, a class average and a batting average are all built from these five functions.
Aggregates turn many rows into one
An aggregate function reads the values of one column from many rows and returns a single value. MySQL's five basic aggregates are COUNT, SUM, AVG, MIN and MAX.
The company wants its salary bill, not 13 salary rows. The first query returns the headcount, the total, the lowest and the highest salary in one row; the others add up Engineering and one order.
Example 1 · The company's salary bill
SELECT COUNT(*) AS employees, SUM(salary) AS salary_bill, MIN(salary) AS lowest, MAX(salary) AS highestFROM employees;
SELECT COUNT(*) AS engineers, SUM(salary) AS engineering_billFROM employeesWHERE dept_id = 1;
SELECT SUM(quantity) AS units, SUM(quantity * unit_price) AS order_valueFROM order_itemsWHERE order_id = 2;+-----------+-------------+----------+-----------+ | employees | salary_bill | lowest | highest | +-----------+-------------+----------+-----------+ | 13 | 1520000.00 | 60000.00 | 250000.00 | +-----------+-------------+----------+-----------+ +-----------+------------------+ | engineers | engineering_bill | +-----------+------------------+ | 5 | 765000.00 | +-----------+------------------+ +-------+-------------+ | units | order_value | +-------+-------------+ | 4 | 1947.00 | +-------+-------------+
What this code does
- The first query reads all 13 employee rows and returns one row of four numbers.
ASgives each result column a readable name. - The second query keeps the 5 Engineering rows first (
WHERE dept_id = 1);COUNTandSUMthen see only those. - The third query works out
quantity * unit_priceon each of order 2's three lines, then adds them: 499 + 650 + 2 × 399 = 1947.
Example 2 · MIN and MAX on dates and text: train bookings
DROP TABLE IF EXISTS train_bookings;CREATE TABLE train_bookings ( pnr CHAR(10) PRIMARY KEY, train_no CHAR(5) NOT NULL, passenger VARCHAR(40) NOT NULL, class VARCHAR(3) NOT NULL, fare DECIMAL(8,2) NOT NULL, booked_on DATE NOT NULL);
INSERT INTO train_bookings VALUES ('4521876301', '12627', 'Kiran Shetty', 'SL', 545.00, '2025-03-02'), ('4521876302', '12627', 'Lata Joshi', '3A', 1435.00, '2025-03-05'), ('4521876303', '12951', 'Mohan Lal', '2A', 3260.00, '2025-02-26'), ('4521876304', '12951', 'Nisha Arora', '3A', 2295.00, '2025-03-09'), ('4521876305', '22691', 'Om Prakash', 'SL', 690.00, '2025-03-01'), ('4521876306', '22691', 'Bindu Thomas', 'SL', 690.00, '2025-03-04');
SELECT COUNT(*) AS tickets, SUM(fare) AS collected, MIN(fare) AS cheapest, MAX(fare) AS costliest, MIN(booked_on) AS first_booking, MAX(booked_on) AS last_booking, MIN(passenger) AS first_by_nameFROM train_bookings;+---------+-----------+----------+-----------+---------------+--------------+---------------+ | tickets | collected | cheapest | costliest | first_booking | last_booking | first_by_name | +---------+-----------+----------+-----------+---------------+--------------+---------------+ | 6 | 8915.00 | 545.00 | 3260.00 | 2025-02-26 | 2025-03-09 | Bindu Thomas | +---------+-----------+----------+-----------+---------------+--------------+---------------+
What this code does
MIN(booked_on)andMAX(booked_on)give the earliest and latest dates; they compare dates as dates, not as text.MIN(passenger)gives the name that comes first in A to Z order: Bindu Thomas, even though she booked last but one.- Each aggregate looks at its own column. The six numbers do not have to come from the same row.
Example 3 · Mistake: a plain column next to an aggregate
-- Goal: the name of the highest-paid employeeSELECT name, MAX(salary)FROM employees;ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'practice.employees.name'; this is incompatible with sql_mode=only_full_group_by
What this code does
MAX(salary)squeezes 13 rows into one, butnamestill has 13 values. MySQL refuses to guess which name to show. This rule is calledONLY_FULL_GROUP_BYand is on by default since MySQL 5.7.5. The message names your own database (herepractice), so your text may differ slightly.- To get the top earner, sort and keep one row:
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1;returns Vikram Rao, 250000.00. (If two people shared the top salary,LIMIT 1would show only one of them.)
An aggregate sees only the rows that pass WHERE, and its argument can be an expression: SUM(quantity * unit_price) multiplies on each row first, then adds. Without aggregates, an app would fetch every row over the network and add the numbers itself.
MIN and MAX also work on dates (earliest and latest) and on text (first and last in A to Z order). SUM and AVG are for numbers.COUNT(*) versus COUNT(column)
COUNT(*) counts rows. COUNT(column) counts only the rows where that column is not NULL, so the two give different answers whenever the column has gaps.
How many customers do we have, and how many gave a phone number? The first query asks both and subtracts one from the other to find the customers with no phone.
Example 4 · Counting rows and counting values
SELECT COUNT(*) AS customers, COUNT(phone) AS with_phone, COUNT(*) - COUNT(phone) AS without_phone, COUNT(1) AS also_all_rowsFROM customers;
SELECT COUNT(*) AS pune_customersFROM customersWHERE city = 'Pune';
SELECT COUNT(*) AS jaipur_customersFROM customersWHERE city = 'Jaipur';+-----------+------------+---------------+---------------+ | customers | with_phone | without_phone | also_all_rows | +-----------+------------+---------------+---------------+ | 10 | 7 | 3 | 10 | +-----------+------------+---------------+---------------+ +----------------+ | pune_customers | +----------------+ | 2 | +----------------+ +------------------+ | jaipur_customers | +------------------+ | 0 | +------------------+
What this code does
- Three customers have
NULLinphone, soCOUNT(phone)is 7 whileCOUNT(*)is 10.COUNT(1)counts every row, likeCOUNT(*). - With
WHERE,COUNT(*)counts only the rows that pass the filter: 2 customers in Pune. - No customer lives in Jaipur, so the count is 0. You still get one row back, never an empty result.
In MySQL's InnoDB engine COUNT(1) is handled the same as COUNT(*) (the 1 is never NULL), so use COUNT(*): everyone reads it at a glance.
COUNT never returns NULL. When no row matches, it returns 0.How aggregates treat NULL
SUM, AVG, MIN and MAX skip NULL values and work only on the values that are present. AVG divides by the number of non-NULL values, not by the number of rows.
One enrollment has no marks yet. The first query counts all entries and the marked ones, then totals and averages the marks; the second asks about an order that does not exist.
Example 5 · NULL marks and an empty result
SELECT COUNT(*) AS entries, COUNT(marks) AS marked, SUM(marks) AS total_marks, AVG(marks) AS avg_of_marked, SUM(marks) / COUNT(*) AS avg_over_all_rows, AVG(COALESCE(marks, 0)) AS avg_missing_as_zeroFROM enrollments;
-- Order 99 does not existSELECT COUNT(*) AS items, SUM(quantity) AS units, COALESCE(SUM(quantity), 0) AS units_or_zeroFROM order_itemsWHERE order_id = 99;+---------+--------+-------------+---------------+-------------------+---------------------+ | entries | marked | total_marks | avg_of_marked | avg_over_all_rows | avg_missing_as_zero | +---------+--------+-------------+---------------+-------------------+---------------------+ | 16 | 15 | 1110 | 74.0000 | 69.3750 | 69.3750 | +---------+--------+-------------+---------------+-------------------+---------------------+ +-------+-------+---------------+ | items | units | units_or_zero | +-------+-------+---------------+ | 0 | NULL | 0 | +-------+-------+---------------+
What this code does
AVG(marks)is 1110 ÷ 15 = 74.0000, because theNULLrow is skipped. Dividing by all 16 rows gives 69.3750, and so doesAVG(COALESCE(marks, 0)), which turns the missing mark into 0.- No row matches order 99.
COUNT(*)says 0,SUMsaysNULL(nothing to add), andCOALESCEreplaces thatNULLwith 0 for the report.
AVG(marks) averages the 15 marked entries and leaves the 16th out; it does not count it as zero. When no row matches, COUNT gives 0 but SUM, AVG, MIN and MAX give NULL. Wrap the result in COALESCE(SUM(x), 0) when your report expects a number.
Predict the output
What does the aggregate return?
Read the code and pick what it prints.
Round 1 of 3
One entry has NULL marks.
AVG(marks); "a missing mark counts as 0" is AVG(COALESCE(marks, 0)).Rounding averages
ROUND(value, digits) keeps only as many decimal places as a reader needs. AVG returns more decimals than any report wants, so averages are almost always rounded.
A batting average of 41.33333333 is not report-ready. The first example rounds averages and a strike rate; a second query rounds an average price to rupees and paise.
Example 6 · Batting average and strike rate, rounded
DROP TABLE IF EXISTS ipl_innings;CREATE TABLE ipl_innings ( innings_id INT PRIMARY KEY, player VARCHAR(40) NOT NULL, runs INT NOT NULL, balls INT NOT NULL);
INSERT INTO ipl_innings VALUES (1, 'Arjun Kale', 63, 41), (2, 'Arjun Kale', 8, 11), (3, 'Arjun Kale', 104, 58), (4, 'Arjun Kale', 35, 30), (5, 'Arjun Kale', 72, 49), (6, 'Arjun Kale', 21, 17);
SELECT COUNT(*) AS innings, SUM(runs) AS runs, AVG(runs) AS raw_avg, ROUND(AVG(runs), 2) AS avg_2dp, ROUND(AVG(runs)) AS avg_whole, ROUND(SUM(runs) * 100 / SUM(balls), 2) AS strike_rateFROM ipl_innings;
SELECT AVG(price) AS raw_avg_price, ROUND(AVG(price), 2) AS avg_priceFROM products;+---------+------+---------+---------+-----------+-------------+ | innings | runs | raw_avg | avg_2dp | avg_whole | strike_rate | +---------+------+---------+---------+-----------+-------------+ | 6 | 303 | 50.5000 | 50.50 | 51 | 147.09 | +---------+------+---------+---------+-----------+-------------+ +---------------+-----------+ | raw_avg_price | avg_price | +---------------+-----------+ | 15064.666667 | 15064.67 | +---------------+-----------+
What this code does
AVG(runs)on anINTcolumn shows 4 decimals;ROUND(..., 2)keeps two andROUND(...)keeps none. The strike rate (runs per 100 balls) is rounded only once, at the end.priceisDECIMAL(10,2), so its raw average shows 6 decimals;ROUND(AVG(price), 2)gives rupees and paise.- This is a simplified average over all innings. Cricket's real batting average divides runs by the number of times the batter got out.
By default MySQL gives an average 4 more decimal places than the column has (the div_precision_increment setting), so an INT average shows 4 decimals and a DECIMAL(10,2) average shows 6.
FORMAT(x, 2) also adds thousands commas, but it returns text. Use it only for the final display, never for more maths.Next step
Check your understanding
12 quick questions