Grouping Data with GROUP BY and HAVING

A common business requirement is to report total sales for each product category, but only for categories that have generated over $50,000 in revenue. This specific query requires combining GROUP BY to aggregate sales per category and HAVING to filter those aggregated results.

Standard aggregate functions like SUM(), COUNT(), or AVG() typically operate on an entire dataset, returning a single summary value. While useful for overall metrics, this approach falls short when you need to analyze data at a more granular level. For instance, calculating the total sales across all products is straightforward, but determining the total sales for each individual product category demands a different strategy. You need a way to break down the dataset into meaningful subsets before applying aggregation.

Partitioning Data with GROUP BY

The GROUP BY clause in SQL provides the mechanism to logically divide a dataset into distinct subsets. It works by collecting rows that have identical values in one or more specified columns into a single summary row. Once these groups are formed, aggregate functions can then be applied independently to each group, yielding a separate aggregate result for every unique combination of values in the grouping columns. This allows for powerful, segmented analysis of your data.

GROUP BY Clause

The GROUP BY clause groups rows that have the same values in specified columns into summary rows, allowing aggregate functions to be applied to each group.

Example: To find the total number of orders for each customer, you would use GROUP BY customer_id.

sqlGrouping Sales by Product Category

Total Sales by Product Category

This bar chart visualizes the total sales amount for each product category after applying a `GROUP BY` clause. Each bar represents a unique category, and its height corresponds to the sum of sales within that category.

Loading chart...

Key Insight: The `GROUP BY` clause successfully aggregates sales data, providing a distinct total for each product category.

When using GROUP BY, there's a strict rule for the columns you can include in your SELECT statement. Any column that appears in the SELECT list must either be part of the GROUP BY clause itself, or it must be an aggregate function (like SUM(), COUNT(), AVG(), MAX(), MIN()) applied to a column. This rule ensures that each row in the result set represents a single, well-defined group, preventing ambiguity in the output.

Check Your Understanding

Which SELECT statement is valid when grouping by department_id?

Filtering Groups with HAVING

While WHERE clauses effectively filter individual rows before they are grouped, they cannot filter based on the results of aggregate functions. For example, if you want to see only product categories that have generated over $50,000 in total sales, a WHERE clause on SUM(sale_amount) would fail because the sum hasn't been calculated yet. This is where the HAVING clause becomes essential. It allows you to apply conditions directly to the aggregated results of your groups.

HAVING Clause

The HAVING clause filters groups based on conditions applied to aggregate functions, after the GROUP BY clause has formed the groups.

Example: To find departments with an average salary greater than $60,000, you would use HAVING AVG(salary) > 60000.

sqlFiltering Groups by Total Sales

WHERE vs. HAVING: Key Differences

Feature	WHERE Clause	HAVING Clause
When Applied	Before grouping (on individual rows)	After grouping (on aggregated groups)
What it Filters	Individual rows	Groups of rows
Conditions Used	Column values (non-aggregate)	Aggregate function results

Understanding when to use WHERE versus HAVING is crucial for correct data filtering.

The SQL Query Execution Order

FROM Clause

The database first identifies the tables involved in the query and combines them (e.g., via JOIN operations) to form the initial dataset.

WHERE Clause

Rows are filtered based on the conditions specified in the WHERE clause. Only rows that satisfy these conditions proceed to the next step.

GROUP BY Clause

The remaining rows are then grouped based on the distinct values in the columns specified by GROUP BY. Aggregate functions are applied to each of these newly formed groups.

HAVING Clause

After grouping and aggregation, the HAVING clause filters these groups based on conditions applied to their aggregate values. Groups that do not meet the HAVING condition are discarded.

SELECT Clause

The expressions in the SELECT list are evaluated for the remaining rows/groups, determining which columns and aggregate results will be displayed.

ORDER BY Clause

Finally, the result set is sorted according to the criteria specified in the ORDER BY clause, arranging the output in a desired sequence.

Check Your Understanding

To filter out individual sales records under $100 before grouping, which clause should you use?

sqlCombining WHERE, GROUP BY, and HAVING

Try It Yourself

Modify the query to find the average sale amount for each product_category for sales made before January 15, 2023, but only for categories where the average sale amount is greater than $15,000.

sql

Key Takeaways

GROUP BY collects rows with identical values in specified columns into summary groups.
When using GROUP BY, SELECT statements must include either grouping columns or aggregate functions.
HAVING filters groups based on conditions applied to aggregate functions, while WHERE filters individual rows before grouping.
The logical order of SQL execution is FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.
To report total sales for product categories exceeding $50,000, you would GROUP BY product_category and use HAVING SUM(sale_amount) > 50000.

Grouping Data with GROUP BY and HAVING

Partitioning Data with GROUP BY

Filtering Groups with HAVING

Ready to keep this from fading?