What are aggregate functions (COUNT, SUM, AVG, etc.)?

Question

IT Interview Practice · Accepted Answer

**Aggregate functions** compute a single value from a set of rows — `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`. They're essential for summarizing data (totals, averages, counts), especially combined with `GROUP BY`.

## The main aggregate functions

```sql
SELECT
  COUNT(*)        AS total_rows,        -- count all rows
  COUNT(email)    AS rows_with_email,    -- count NON-NULL emails (ignores NULLs!)
  SUM(amount)     AS total_amount,       -- sum of a column
  AVG(price)      AS average_price,      -- average
  MIN(price)      AS lowest,             -- minimum
  MAX(price)      AS highest             -- maximum
FROM orders;
```

Each function reduces many rows to one value. `SUM`/`AVG` work on numbers; `MIN`/`MAX` on numbers, dates, or text; `COUNT` on anything.

## COUNT(*) vs COUNT(column) — an important difference

```sql
COUNT(*)         -- counts ALL rows (including those with NULLs)
COUNT(email)     -- counts only rows where email is NOT NULL
COUNT(DISTINCT country)  -- counts UNIQUE non-null values
```

**`COUNT(*)`** counts all rows, but **`COUNT(column)`** counts only rows where that column is **not NULL** — a subtle but important distinction. `COUNT(DISTINCT col)` counts unique values.

## Aggregates with GROUP BY (per-group summaries)

```sql
-- aggregate PER GROUP instead of the whole table
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
GROUP BY country;          -- one result row per country
-- → for each country: how many users and their average age
```

Without `GROUP BY`, an aggregate summarizes the entire result into one row. With `GROUP BY`, it produces one summary row **per group** — the typical, powerful use.

## A common gotcha: aggregates and non-aggregated columns

```sql
-- ❌ ERROR — can't mix an aggregate with a non-grouped column
SELECT name, COUNT(*) FROM users;        -- which name? ambiguous
-- ✅ either group by it, or only select aggregates
SELECT country, COUNT(*) FROM users GROUP BY country;
```

## Why it matters

Aggregate functions are essential for **summarizing and analyzing data** — computing totals, averages, counts, and extremes is a constant need in reporting, analytics, dashboards, and application logic, so understanding them is fundamental SQL knowledge.

Knowing the main functions (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`) and how they reduce sets of rows to single values is necessary for any data summarization.

An important, commonly-misunderstood detail is the **`COUNT(*)` vs `COUNT(column)` distinction** (COUNT(*) counts all rows, COUNT(column) counts only non-NULL values — which can give different, surprising results), along with `COUNT(DISTINCT)` for unique values.

Most powerfully, aggregates combine with **`GROUP BY`** to produce per-group summaries (counts/averages per category) — the foundation of analytical queries.

Understanding the **gotcha** that you can't mix aggregates with non-grouped columns (a common error) is also important.

Since summarizing data is a ubiquitous database task (every report, dashboard, and analytics query uses aggregates), and since understanding the functions, the COUNT subtleties, and especially their combination with GROUP BY is foundational to data analysis in SQL, mastering aggregate functions is core, frequently-applied knowledge essential for working with data — bridging basic querying and the analytical, summarizing power that makes SQL valuable for reporting and insights.

The main aggregate functions

COUNT(*) vs COUNT(column) — an important difference

Aggregates with GROUP BY (per-group summaries)

A common gotcha: aggregates and non-aggregated columns

Why it matters

Related questions