How to Use GROUP BY in MySQL to Aggregate Data

This article provides a straightforward guide on how to use the GROUP BY clause in MySQL to organize and aggregate data. You will learn the fundamental syntax of the clause, how to combine it with essential aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX(), and how to filter your grouped results using the HAVING clause.

Understanding the GROUP BY Clause

In MySQL, the GROUP BY clause is used to collaborate with aggregate functions to group the result-set by one or more columns. Instead of returning every single row in a table, GROUP BY condenses identical data into single summary rows.

The basic syntax of a GROUP BY query is as follows:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

The GROUP BY clause must place after the FROM and WHERE clauses, but before the ORDER BY and LIMIT clauses.

Using GROUP BY with Aggregate Functions

To make GROUP BY useful, you must pair it with an aggregate function. Below are the most common scenarios using a hypothetical sales table containing columns for category, product_name, and price.

1. Counting Rows with COUNT()

To find the total number of products sold in each category, use the COUNT() function:

SELECT category, COUNT(*) AS total_products
FROM sales
GROUP BY category;

2. Calculating Totals with SUM()

To calculate the total revenue generated by each product category, use the SUM() function:

SELECT category, SUM(price) AS total_revenue
FROM sales
GROUP BY category;

3. Finding Averages with AVG()

To determine the average price of items within each category, use the AVG() function:

SELECT category, AVG(price) AS average_price
FROM sales
GROUP BY category;

4. Finding Extreme Values with MIN() and MAX()

To identify the cheapest and most expensive product in each category, use MIN() and MAX():

SELECT category, MIN(price) AS lowest_price, MAX(price) AS highest_price
FROM sales
GROUP BY category;

Grouping by Multiple Columns

You can group your data by more than one column to create highly specific subgroups. For example, if you want to see the total sales broken down by both category and region:

SELECT category, region, SUM(price) AS total_sales
FROM sales
GROUP BY category, region;

This query returns a unique row for every unique combination of category and region.

Filtering Grouped Data with HAVING

When working with aggregated data, you cannot use the WHERE clause to filter the results of your aggregate functions. Instead, you must use the HAVING clause.

While the WHERE clause filters individual rows before they are grouped, the HAVING clause filters the groups after the GROUP BY operation has been performed.

For example, to find categories that have generated more than $5,000 in total revenue:

SELECT category, SUM(price) AS total_revenue
FROM sales
GROUP BY category
HAVING total_revenue > 5000;