Group rows into meaningful categories

GROUP BY collapses individual rows into summary groups. Without it, you can count every order in your database but not how many belong to each customer. That gap is what GROUP BY closes.

GROUP BY is the organizing principle behind aggregation. It tells SQL: "Put all rows with the same value into the same bucket, then calculate something for each bucket."

Without GROUP BY, an aggregate function operates on the entire table and returns a single row. With GROUP BY, you get one row per unique value in the grouping column.

GROUP BY transforms your data by organizing rows into buckets based on shared values, then summarizing each bucket independently.

When you GROUP BY region, SQL creates three buckets: one for "North" (3 rows), one for "South" (2 rows), and one for "East" (1 row). Each bucket is then summarized independently.

GROUP BY has strict rules about what you can select. Understanding these rules prevents common errors and helps you write correct queries.

When you use GROUP BY, every column in your SELECT must either:

1. Be in the GROUP BY clause, OR 2. Be inside an aggregate function (COUNT, SUM, AVG, etc.)

Understanding the execution sequence helps you write efficient GROUP BY queries.

SELECT
  ___,
  ___(*) AS num_products
FROM products
___ category

GROUP BY is the single most important clause in analytical SQL. Every dashboard, report, and summary table ultimately relies on it to collapse raw rows into meaningful categories.

The golden rule is simple: every column in SELECT must either be in GROUP BY or wrapped in an aggregate function. Violating this rule produces an error, and rightly so, because SQL needs to know how to handle multi-valued columns.

Count rows, values, or unique entries

COUNT() is the simplest and most frequently used aggregate function. It counts things. Depending on how you use it, you can count rows, count non-empty values, or count unique values.

COUNT has three forms, each answering a different question about your data. Choosing the right one depends on what you need to measure.

COUNT(*) counts every row in the group, regardless of what values are in those rows. It counts rows where columns are empty and rows where columns have values.

With GROUP BY, COUNT(*) tells you how many rows are in each group:

COUNT(column_name) counts only rows where that specific column is not empty (not NULL). This is useful when some rows have missing data.

COUNT(*) returns 5 (all rows). COUNT(email) returns 3 (only rows with an email). The NULL entries represent missing values which COUNT(column) skips.

Adding DISTINCT inside COUNT tells SQL to count only unique values, ignoring duplicates.

Knowing when to use each COUNT variant helps you answer different business questions accurately.

The three variants of COUNT answer different questions. Choose the right one for what you need to measure:

Each COUNT variant has different performance characteristics based on the work required.

COUNT(*)

Extremely fast: only counts rows, no value checks needed

COUNT(column)

Checks each value for NULL before counting

COUNT(DISTINCT)

Builds a set of unique values, then counts the set

Return type

COUNT always returns an integer, never NULL

SELECT
  region,
  COUNT(*) AS total_orders,
  ___(___ customer_id) AS unique_customers
FROM orders
GROUP BY region

COUNT(*), COUNT(column), and COUNT(DISTINCT column) each answer a different question. Choosing the right variant prevents subtle errors in data quality reports and business metrics.

A common real-world use case is audit reporting: COUNT(*) gives total records, COUNT(email) gives records with a value, and COUNT(DISTINCT customer_id) gives unique entities, all in one query.

Calculate totals and averages per group

SUM() adds up all the values in a column. It is used for any question involving totals: total revenue, total quantity, total hours, total anything numeric.

SUM adds up numeric values to produce totals. It works with or without GROUP BY, calculating grand totals or group-level totals.

The simplest SUM query adds up every value in a column across the entire table.

Without GROUP BY, SUM adds up every row in the table, giving you a grand total.

The real power of SUM appears when combined with GROUP BY. Now you can see totals broken down by category.

SUM behaves differently depending on whether you include GROUP BY. Compare the two approaches:

SUM ignores NULL values. If a column has some NULL entries, they are simply skipped in the calculation. If ALL values are NULL, SUM returns NULL (not zero).

SUM has specific behaviors around NULL values and data types that affect your results.

AVG calculates arithmetic means to find typical values. Understanding how it handles NULLs is crucial for accurate analysis.

AVG() calculates the arithmetic mean: sum of all values divided by the count of values. It is essential for understanding typical values: average order size, average response time, average salary.

AVG without GROUP BY calculates the mean across all rows in the table.

When combined with GROUP BY, AVG calculates a separate mean for each group.

AVG() is equivalent to SUM() / COUNT(), but there is a subtle difference with NULL values:

The manual calculation using SUM/COUNT handles NULLs the same way as AVG.

AVG has important behaviors around NULL handling that can affect your calculations.

Since AVG is sensitive to outliers, be cautious when interpreting results from skewed datasets.

SELECT
  region,
  ___(amount) AS total_rev,
  ___(amount) AS avg_rev
FROM cloud_costs
GROUP BY region

SUM and AVG both ignore NULL values. If a column has NULLs, they are silently excluded from the calculation.

AVG can produce misleading results on skewed data. Consider using percentiles alongside averages for a fuller picture.

Combining SUM and AVG in the same GROUP BY query gives you both the total and typical value per group in one pass.

Find the highest and lowest values

MIN() and MAX() find the smallest and largest values in a column. They work with numbers, dates, and even text (alphabetical order).

MIN and MAX find the smallest and largest values in your data. They scan through groups to identify extremes.

MIN() scans through all values in each group, keeping track of the smallest one found so far:

MAX() works the same way, but keeps track of the largest value in each group:

Now the largest price per category is selected instead. Electronics surfaces 599 and Clothing surfaces 89, the opposite extremes from MIN.

Together with GROUP BY, they let you find price ranges within each category:

MIN and MAX work beyond numbers. They handle dates for time-based analysis and even text for alphabetical ordering.

MIN and MAX are extremely useful for date analysis: finding first and last events, date ranges, and activity spans.

When applied to text columns, MIN returns the alphabetically first value and MAX returns the alphabetically last value.

Compare how MIN and MAX behave on the same data:

MIN and MAX are often used alongside other aggregate functions to give a complete picture:

MIN and MAX have straightforward execution but work differently depending on data types.

SELECT
  category,
  ___(price) AS lowest,
  ___(price) AS highest
FROM products
GROUP BY category

MIN and MAX scan all values in the group to find extremes. NULL values are silently excluded from the comparison.

For text columns, MIN returns the alphabetically first value and MAX returns the last. This comparison is case-sensitive.

Combining MIN, MAX, and AVG in one query shows the full range and central tendency, giving a quick data distribution summary.

Filter groups after aggregation

HAVING filters groups after aggregation, just as WHERE filters rows before aggregation. You need HAVING when your filter condition involves an aggregate function.

The key distinction: WHERE cannot reference aggregate functions because it runs before the aggregation happens. HAVING runs after aggregation, so it CAN reference aggregated values.

HAVING exists because WHERE cannot filter on aggregate results. Understanding when to use each is essential for correct queries.

You cannot use aggregate functions in WHERE because it runs before aggregation. HAVING runs after aggregation, so it can reference aggregated values:

WHERE filters individual rows (before grouping). HAVING filters entire groups (after grouping).

HAVING conditions can use any aggregate function, letting you filter on totals, averages, counts, or any calculated metric.

This shows only customers who have spent more than $1,000 total. Customers who spent less are filtered out by HAVING.

WHERE and HAVING can work together in the same query. Understanding the execution order helps you write efficient and correct queries.

You can use both in the same query. WHERE filters rows first, then GROUP BY creates groups, then HAVING filters groups:

First, WHERE keeps only completed orders (filtering rows). Then, GROUP BY counts completed orders per region. Finally, HAVING keeps only regions with more than 50 completed orders (filtering groups).

These guidelines help you choose between WHERE and HAVING for optimal query performance.

SQL clauses execute in a specific order. Understanding this sequence clarifies why HAVING can reference aggregates but WHERE cannot.

1. FROM

Start with the table

2. WHERE

Filter individual rows

3. GROUP BY

Create groups from remaining rows

4. HAVING

Filter groups based on aggregate values

5. SELECT

Calculate and return results

SELECT
  department,
  AVG(metric_value) AS avg_metric
FROM employee_metrics
GROUP BY department
___ ___(metric_value) > ___

HAVING is the clause that unlocks group-level filtering. Without it, you would need subqueries to accomplish what HAVING does in a single, readable step.

The WHERE and HAVING combination is extremely powerful: WHERE eliminates unwanted rows before any grouping occurs, reducing the data the database must aggregate. HAVING then eliminates groups whose summary metrics do not meet your criteria.

If your HAVING condition does not reference an aggregate function, move it to WHERE instead. Filtering rows before grouping is always faster than filtering groups after aggregation.