How to Use GROUP BY and HAVING in MySQL

When analyzing data in MySQL, you often need to group data by certain criteria, then perform calculations on each group. For example, calculating total sales per category, calculating the average salary per department, or finding the number of customers per city. Two clauses that are key to these needs are GROUP BY and HAVING. Let's study both in depth.

What Is GROUP BY?

GROUP BY is a SQL clause used to group rows that have the same value in a certain column into one summary row. GROUP BY is usually combined with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX().

Preparing the Example Data

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    date DATE NOT NULL
) ENGINE=InnoDB;

INSERT INTO sales (product_name, category, quantity, unit_price, date) VALUES
('ASUS Laptop', 'Electronics', 2, 8500000, '2024-01-05'),
('Logitech Mouse', 'Electronics', 10, 350000, '2024-01-06'),
('Python Book', 'Books', 5, 120000, '2024-01-07'),
('Mechanical Keyboard', 'Electronics', 3, 750000, '2024-01-08'),
('MySQL Book', 'Books', 8, 95000, '2024-01-09'),
('Work Desk', 'Furniture', 1, 2500000, '2024-01-10'),
('Ergonomic Chair', 'Furniture', 2, 3200000, '2024-01-11'),
('27 Inch Monitor', 'Electronics', 1, 4500000, '2024-01-12'),
('Laravel Book', 'Books', 12, 110000, '2024-01-13'),
('Filing Cabinet', 'Furniture', 1, 1800000, '2024-01-14');

Basic GROUP BY Usage

Count the number of products and total revenue per category:

-- Calculate total sales per category
SELECT
    category,
    COUNT(*) AS transaction_count,
    SUM(quantity) AS total_units_sold,
    SUM(quantity * unit_price) AS total_revenue,
    AVG(unit_price) AS average_price
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

GROUP BY with Multiple Columns

You can group by more than one column at once:

-- Group by category and month
SELECT
    category,
    MONTH(date) AS month,
    YEAR(date) AS year,
    SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY category, YEAR(date), MONTH(date)
ORDER BY year, month, category;

What Is HAVING?

HAVING is a clause used to filter the result of GROUP BY. Its function is similar to WHERE, but the main difference is:

• WHERE: Filters rows before they are grouped and can't use aggregate functions.
• HAVING: Filters the result after grouping and can use aggregate functions.

Using HAVING

Show only categories with total revenue above 5 million:

-- Filter groups using HAVING
SELECT
    category,
    SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY category
HAVING total_revenue > 5000000
ORDER BY total_revenue DESC;

Combining WHERE and HAVING

WHERE and HAVING can be used together. WHERE filters the raw data first, then HAVING filters the aggregate result:

-- WHERE filters the data first, HAVING filters the group result
SELECT
    category,
    COUNT(*) AS product_count,
    SUM(quantity * unit_price) AS total_revenue
FROM sales
WHERE date >= '2024-01-07'      -- filter by date first
GROUP BY category
HAVING product_count >= 2         -- filter the group result
ORDER BY total_revenue DESC;

Frequently Used Aggregate Functions

• COUNT(*): Counts the number of rows in each group.
• COUNT(column): Counts rows whose value is not NULL.
• SUM(column): Sums the values in a column.
• AVG(column): Calculates the average of a column's values.
• MAX(column): Gets the largest value in the group.
• MIN(column): Gets the smallest value in the group.

-- All aggregate functions at once
SELECT
    category,
    COUNT(*) AS item_count,
    SUM(quantity) AS total_units,
    ROUND(AVG(unit_price), 2) AS avg_price,
    MAX(unit_price) AS highest_price,
    MIN(unit_price) AS lowest_price
FROM sales
GROUP BY category;

GROUP BY with ROLLUP

MySQL 8 supports WITH ROLLUP to automatically add subtotal and grand total rows:

-- Add a grand total row with ROLLUP
SELECT
    COALESCE(category, 'GRAND TOTAL') AS category,
    SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY category WITH ROLLUP;

SQL Query Execution Order

It's important to understand the execution order of SQL clauses so you don't get confused about WHERE vs HAVING:

1. FROM – determine the source table
2. WHERE – filter individual rows
3. GROUP BY – group the rows
4. HAVING – filter the groups
5. SELECT – choose the columns to display
6. ORDER BY – sort the result
7. LIMIT – limit the number of rows

Conclusion

GROUP BY and HAVING are two complementary clauses in MySQL. GROUP BY groups data by a certain column's value, while HAVING filters the grouping result based on an aggregate condition. The key difference: use WHERE to filter data before grouping, and use HAVING to filter after grouping. Master both clauses and you'll be able to produce powerful analytical reports straight from a SQL query.