SQL Having Clause

In this lesson, you will learn how to filter the groups returned in SQL.

The HAVING Clause In SQL

The HAVING clause is used in conjunction with the GROUP BY clause to filter the results of aggregate functions applied to grouped rows. It allows you to apply a condition to the aggregated data, specifying which groups should be included in the result set based on the result of an aggregate function.

So, the groups returned by the GROUP BY clause can be filtered by the HAVING clause.

Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s); 

Preparing A Database

Since you will learn how to filter the groups returned, you must have a database table with some records having common data to practice grouping and filtering them. Execute the following query to create the database.

-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;

-- Create a new database called "demo"
CREATE DATABASE demo;

-- Select the "demo" database
USE demo;

-- Create a new table called "employees" inside the selected database
CREATE TABLE employees (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    gender VARCHAR(20),
    country VARCHAR(100),
    salary DECIMAL(7,2)
);

-- Insert 10 records in the "employees" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO employees VALUES (null, "Emily", "female", "Denmark", 800);
INSERT INTO employees VALUES (null, "Stacy", "female", "Australia", 900);
INSERT INTO employees VALUES (null, "John", "male", "Canada", 750);
INSERT INTO employees VALUES (null, "Lana", "female", "Germany", 1000);
INSERT INTO employees VALUES (null, "Ronaldo", "male", "Brazil", 845);
INSERT INTO employees VALUES (null, "Louise", "female", "France", 700);
INSERT INTO employees VALUES (null, "James", "male", "Canada", 920);
INSERT INTO employees VALUES (null, "Maria", "female", "Brazil", 850);
INSERT INTO employees VALUES (null, "Mia", "female", "Canada", 780);
INSERT INTO employees VALUES (null, "Luke", "male", "Japan", 880);
    

This is the employees table that we created in the demo database.

HAVING Clause Example

The following query returns the average salary in each country that is above 800.
We group the rows that have the same country, and then calculate the average salary in each group, and then filter them and return only those who have an average salary above 800.

SELECT country AS 'Country', AVG(salary) AS Average_Salaries
FROM employees
GROUP BY country
HAVING Average_Salaries > 800;
    

WHERE Clause vs HAVING Clause

In SQL, the WHERE clause and HAVING clause are both used to filter the results of a query, but they are applied at different stages of the query process and have distinct purposes.

WHERE Clause Usage

• The WHERE clause is used to filter rows based on conditions applied to columns before the data is grouped or aggregated.
• It is used with the SELECT, UPDATE, and DELETE statements to specify a condition that must be met for a row to be included in the result set or affected by the query.
• It is applied before the GROUP BY clause in a SELECT statement.

HAVING Clause Usage

• The HAVING clause is used to filter the results of aggregate functions (such as COUNT, SUM, AVG, etc.) after the data has been grouped by one or more columns.
• It is used in conjunction with the GROUP BY clause to filter the grouped results based on a condition.
• HAVING is typically used when you want to filter the results of aggregate functions, and it comes after the GROUP BY clause.

Filter Grouped Rows Exercise

1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.

2- Write a query that creates a table called "films" and check whether it exists or not. the table should contain the following fields:

• id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
• title VARCHAR(100)
• category VARCHAR(30)
• ranking FLOAT

3- Write a query that inserts 10 rows in the "films" table.

4- Write a query that groups the films based on their categories, and then returns the categories that have a ranking bigger than or equal to 8 in descending order.

DROP DATABASE IF EXISTS training;

CREATE DATABASE training;

USE training;

CREATE TABLE IF NOT EXISTS films (
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    category VARCHAR(30),
    ranking FLOAT
);

INSERT INTO films (title, category, ranking) VALUES
('The Shawshank Redemption', 'Drama', 9.3),
('Inception', 'Sci-Fi', 8.8),
('The Godfather', 'Crime', 9.2),
('Titanic', 'Romance', 7.8),
('The Dark Knight', 'Action', 9.0),
('Forrest Gump', 'Romance', 7.5),
('Pulp Fiction', 'Crime', 8.9),
('The Matrix', 'Sci-Fi', 8.7),
('The Notebook', 'Romance', 8.0),
('The Silence of the Lambs', 'Crime', 8.6);

SELECT category As 'Category', AVG(ranking) AS 'Average_Ranking'
FROM films
GROUP BY category
HAVING Average_Ranking >= 8
ORDER BY Average_Ranking DESC;