MySQL HAVING Clause

MySQL HAVING Clause

The MySQL HAVING clause is used to filter groups of rows based on a specified condition. This is essential for applying conditions to aggregated data.

---

Syntax

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

The HAVING clause has the following components:

• column1: The column by which to group the results.
• aggregate_function(column2): An aggregate function applied to the column(s) being grouped.
• table_name: The name of the table from which to retrieve the data.
• condition: An optional condition to filter the rows before grouping.

---

Example MySQL HAVING Clause

Let's look at some examples of the MySQL HAVING clause:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

This query creates a table named employees with columns for id, first_name, last_name, department, and salary.

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Doe', 'HR', 50000.00),
       ('Jane', 'Smith', 'IT', 60000.00),
       ('Jim', 'Brown', 'IT', 55000.00),
       ('Jake', 'White', 'HR', 52000.00),
       ('Jill', 'Green', 'Marketing', 45000.00);

This query inserts five rows into the employees table.

Step 4: Grouping and Summing with HAVING

Group rows by a column, sum values, and apply a condition using HAVING:

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING total_salary > 100000;

This query groups rows by the department column, sums the salary for each department, and filters the groups to only include those with a total salary greater than 100000.

Step 5: Using HAVING with COUNT

Group rows by a column, count them, and apply a condition using HAVING:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING employee_count > 1;

This query groups rows by the department column, counts the number of employees in each department, and filters the groups to only include those with more than one employee.

Step 6: Using HAVING with AVG

Group rows by a column, average values, and apply a condition using HAVING:

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING average_salary > 50000;

This query groups rows by the department column, calculates the average salary for each department, and filters the groups to only include those with an average salary greater than 50000.

Step 7: Using HAVING with WHERE

Filter rows with WHERE, group them, and apply a condition using HAVING:

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING employee_count > 1;

This query filters rows where the salary is greater than 50000, groups the remaining rows by the department column, counts the number of employees in each department, and filters the groups to only include those with more than one employee.

Step 8: Using HAVING with ORDER BY

Group rows, apply a condition using HAVING, and sort the results:

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING total_salary > 100000
ORDER BY total_salary DESC;

This query groups rows by the department column, sums the salary for each department, filters the groups to only include those with a total salary greater than 100000, and sorts the results by the total salary in descending order.

---

Conclusion

The MySQL HAVING clause is a powerful tool for filtering groups of rows based on conditions. Understanding how to use the HAVING clause is essential for effective data analysis and reporting in MySQL.