beginnerCore SQL100% Free

SQL Aggregate Functions

Definition

SQL aggregate functions perform calculations on a set of rows and return a single summary value, including COUNT, SUM, AVG, MIN, and MAX.

Introduction to SQL Aggregate Functions

Aggregate functions are the mathematical backbone of SQL analytics. They transform a set of rows into a single computed value — the count, total, average, smallest, or largest. Combined with GROUP BY, they power business reporting, dashboards, and data analysis.

Syntax

SQL

SELECT
  COUNT(*) AS total_rows,
  COUNT(DISTINCT col) AS unique_vals,
  SUM(col) AS total,
  AVG(col) AS average,
  MIN(col) AS minimum,
  MAX(col) AS maximum
FROM table
GROUP BY group_col;

Examples

Sales Summary Dashboard

SELECT
  department,
  COUNT(*) AS employees,
  SUM(salary) AS total_payroll,
  AVG(salary) AS avg_salary,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary
FROM employees
GROUP BY department;

A classic salary analytics query using all five aggregate functions in one GROUP BY query.

Try in Playground

COUNT DISTINCT — Unique Values

SELECT
  COUNT(*) AS total_orders,
  COUNT(DISTINCT customer_id) AS unique_customers,
  COUNT(DISTINCT product_id) AS unique_products
FROM orders;

COUNT(*) counts all rows. COUNT(DISTINCT col) counts only unique non-NULL values. Essential for customer analytics.

Try in Playground

Common Mistakes

COUNT(*) vs COUNT(col) — COUNT(*) counts all rows including NULLs; COUNT(col) skips NULL values

AVG ignores NULL values — this can skew averages if NULLs represent zero

Using aggregate functions in WHERE instead of HAVING

SUM on non-numeric columns — always verify the column data type

Frequently Asked Questions

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts all rows in the group, including rows with NULL values. COUNT(column) counts only rows where the specified column is NOT NULL. If a column has 10 NULL values in 100 rows, COUNT(*) = 100 but COUNT(column) = 90.

Does AVG ignore NULL values in SQL?

Yes. AVG automatically ignores NULL values — it divides the sum by the count of non-NULL values only. This can produce misleading results if NULLs represent zero (e.g., no sales). Use AVG(COALESCE(column, 0)) to treat NULLs as zero.

Can aggregate functions be nested in SQL?

Standard SQL does not allow direct nesting like AVG(SUM(col)). However, you can achieve this with subqueries or CTEs: SELECT AVG(dept_total) FROM (SELECT department, SUM(salary) AS dept_total FROM employees GROUP BY department) dept_sums;

Practice This in the SQL Playground

Write real queries, see live results, and master SQL Aggregate Functions hands-on. 100% free.

Open SQL Playground Browse All Lessons