Having clause in SQL

The HAVING clause in SQL is used to filter records that have been grouped by a GROUP BY clause. It is similar to the WHERE clause, but it is used for groups rather than individual rows. The HAVING clause is often used with aggregate functions such as COUNT, SUM, AVG, MAX, and MIN.

SQL Syntax

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

Example: Let's say we have a table sales that records the sales made by employees in different regions.

Create a sales table and its columns.

SQL

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    employee_id INT,
    region VARCHAR(50),
    amount DECIMAL(10, 2)
);

Load the data into the Sales table.

SQL

INSERT INTO sales (
sale_id, employee_id, region, amount) 
VALUES
(1, 1, 'North', 500),
(2, 2, 'South', 800),
(3, 1, 'North', 700),
(4, 3, 'East', 200),
(5, 2, 'South', 400),
(6, 3, 'East', 600),
(7, 1, 'North', 300);

By executing the below query we can see the data from the sales table.

SQL

Select * From Sales;

After running the above query, the result should be given below:

Table: sales

Sale ID	Employee ID	Region	Amount
1	1	North	500
2	2	South	800
3	1	North	700
4	3	East	200
5	2	South	400
6	3	East	600
7	1	North	300

Example with GROUP BY Clause Suppose we want to find the total sales amount for each region where the total sales exceed 1000.

SQL

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) > 1000;

The result of the above query is shown below:

Region	Total Sales
North	1500
South	1200

Example with GROUP BY and WHERE Clauses Suppose we want to find the total sales amount for each employee in the North region where the total sales exceed 1000.

SQL

SELECT employee_id, SUM(amount) AS total_sales
FROM sales
WHERE region = 'North'
GROUP BY employee_id
HAVING SUM(amount) > 1000;

The result of the above query is shown below:

Employee ID	Total Sales
1	1500

In this example:

The WHERE clause filters the rows to include only those in the 'North' region.
The GROUP BY clause groups the filtered rows by employee_id.
The HAVING clause filters these groups to include only those with a total sales amount greater than 1000.

Previous Next