COUNT, SUM, AVG, MIN and MAX

Concept

An aggregate function takes many rows and returns one value. Used without GROUP BY, it collapses the entire result into a single row.

Syntax

SELECT aggregate_function(column_or_expression)
FROM   table_name
WHERE  condition;

Example

SELECT COUNT(*)              AS employee_count,
       COUNT(dept_id)        AS with_department,
       COUNT(DISTINCT dept_id) AS distinct_departments,
       SUM(salary)           AS total_payroll,
       ROUND(AVG(salary), 2) AS average_salary,
       MIN(salary)           AS lowest,
       MAX(salary)           AS highest,
       MAX(salary) - MIN(salary) AS salary_spread
FROM   employees;

-- Aggregates respect WHERE, which runs first
SELECT COUNT(*)   AS active_employees,
       SUM(salary) AS active_payroll
FROM   employees
WHERE  status = 'active';

Explanation

On the sample data COUNT(*) returns 8 but COUNT(dept_id) returns 7, because Nikhil has no department. That gap is the fastest way to measure missing data in a column:

SELECT COUNT(*) - COUNT(email) AS missing_emails FROM employees;

MIN and MAX are not limited to numbers. On a DATE they give the earliest and latest; on text they follow the collation's sort order:

SELECT MIN(hire_date) AS first_hire,
       MAX(hire_date) AS latest_hire,
       MIN(last_name) AS alphabetically_first
FROM   employees;

Important rules

• Every aggregate except COUNT(*) ignores NULL.
• AVG(col) is SUM(col) / COUNT(col), not SUM(col) / COUNT(*). Rows with NULL are not in the denominator.
• Aggregates cannot appear in WHERE. Filtering on an aggregate is what HAVING is for.
• Aggregating an empty set gives NULL for SUM, AVG, MIN and MAX, but 0 for COUNT.
• Aggregates cannot be nested directly: MAX(AVG(salary)) is invalid without a subquery or a GROUP BY underneath it.

Common mistakes

• Reporting AVG(bonus) as "average bonus per employee" when employees without a bonus are excluded from the divisor. Use AVG(COALESCE(bonus, 0)) if they should count as zero.
• Writing WHERE COUNT(*) > 5. It is a syntax error; use HAVING.
• Assuming SUM of an empty result is 0. It is NULL - wrap it: COALESCE(SUM(total), 0).
• Using COUNT(column) when the intent was to count rows, and silently under counting.
• Mixing a bare column with an aggregate and no GROUP BY, which is an error in standard SQL and a silently arbitrary value in loose MySQL modes.

Best practices

• Use COUNT(*) to count rows and COUNT(col) only when you specifically mean "values present".
• Wrap aggregates that feed a display in COALESCE(..., 0) so an empty result shows zero, not blank.
• Round money aggregates explicitly.
• State the NULL policy in the column alias: avg_salary_of_paid_staff beats avg_salary.

Practice

1. Find the total, average, smallest and largest order value in the sample orders table.
2. Count how many customers have placed at least one order, and how many exist in total.
3. Explain the difference between COUNT(*), COUNT(dept_id) and COUNT(DISTINCT dept_id) on the sample employees.