Sorting and Grouping Data

Understand how to sort your data using the ORDER BY clause and group data using the GROUP BY clause. We'll cover different sorting orders and aggregate functions for grouped data.

⬅ Previous Next ➡

Filtering Grouped Data with the HAVING Clause

The HAVING clause in MySQL is used to filter the results of a GROUP BY query. It acts like a WHERE clause, but it operates on the grouped data after the aggregation has been performed.

Explanation: Filtering Grouped Data with HAVING Clause

When you use GROUP BY, you're creating groups of rows based on the values in one or more columns. Aggregation functions (like COUNT(), SUM(), AVG(), MIN(), MAX()) are then applied to each of these groups, producing a single row of results for each group.

The WHERE clause filters rows before they are grouped. The HAVING clause, on the other hand, filters the grouped results based on the aggregated values. This is crucial because you can't use a WHERE clause to filter based on the results of aggregation functions.

In essence, HAVING allows you to specify conditions that must be met by the aggregate values for a group to be included in the final result set.

Using HAVING Clause to Filter Grouped Data

The syntax for using the HAVING clause is as follows:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING aggregate_function(column3) condition;

The HAVING clause follows the GROUP BY clause and contains a condition that is evaluated for each group. Only groups that satisfy the condition are included in the result set.

Example

Let's say we have a table called orders with the following structure:

 CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    product_id INT,
    quantity INT,
    order_date DATE
);

INSERT INTO orders (customer_id, product_id, quantity, order_date) VALUES
(1, 101, 2, '2023-01-15'),
(1, 102, 1, '2023-02-20'),
(2, 101, 3, '2023-03-10'),
(2, 103, 2, '2023-04-05'),
(3, 102, 5, '2023-05-12'),
(3, 101, 1, '2023-06-01');

We want to find all customers who have placed a total quantity of orders greater than 3.

 SELECT customer_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY customer_id
HAVING SUM(quantity) > 3;

This query first groups the orders by customer_id. Then, it calculates the SUM(quantity) for each customer, aliasing it as total_quantity. Finally, the HAVING clause filters the results, only including customers where the total_quantity is greater than 3.

Key Takeaways

HAVING filters grouped data after aggregation.
WHERE filters rows before grouping.
HAVING is essential for filtering based on aggregate function results.

⬅ Previous Next ➡