-- Basic aggregate report

SELECT

COUNT(*) AS total_employees,

COUNT(bonus) AS employees_with_bonus,

SUM(salary) AS total_payroll,

AVG(salary) AS avg_salary,

MIN(salary) AS min_salary,

MAX(salary) AS max_salary

FROM employees;

COUNT(*) counts all rows including those with NULL values

COUNT(column) counts only non-NULL values in that column

SUM, AVG, MIN, MAX automatically ignore NULL values

If ALL values are NULL, aggregate returns NULL (not zero)

Aggregates return a single value (or one value per group with GROUP BY)

Cannot mix aggregate and non-aggregate columns without GROUP BY

Strict about mixing aggregates and non-aggregates

More lenient with GROUP BY but this is non-standard

Standard aggregate behavior - strict about GROUP BY

Core references in this topic include COUNT, SUM, AVG. Learn what each one does, when to use it, and the execution or engine rules that matter.

COUNT

Counts rows or non-NULL values depending on the argument.

COUNT(*)

SUM

Adds numeric values together across the current group or window frame.

SUM(revenue)

AVG

Calculates the arithmetic mean of numeric values.

AVG(salary)

MIN / MAX

Returns the smallest or largest value in the current set.

MIN(order_date), MAX(order_date)

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

IS NULL / IS NOT NULL

Tests whether a value is missing. SQL NULL semantics require dedicated NULL predicates.

manager_id IS NULL

Never use = NULL or != NULL.

LIKE