SQL HAVING Clause

1. Introduction

A SQL HAVING clause is a part of a SQL SELECT statement that filters out the rows that don’t match the aggregating conditions. Here is the syntax for the HAVING clause in a SELECT statement:

HAVING Syntax

SELECT select_list [ INTO new_table ]
[ FROM table_source ] 
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY columns ]

Note: the Having clause is right after a Group By clause if it exists and before an Order By clause. The search_condition after HAVING must return a boolean value.

In this example, I will demonstrate the SQL HAVING clause:

• with aggregate functions: count, avg, sum, min, and max.
• with a GROUP BY clause.
• without a GROUP BY clause.
• with more than one aggregate functions.
• with an ORDER BY clause.

2. Technologies Used

The example code in this article was built and run using:

• SQL
• MS SQL Server
• SQuirrel Client 3.9.0

3. Setup Data

In this step, I will create two database tables:

• categories – has two columns: category_id and category_name. It has three records.
• products – has five columns: product_id, category_id, product_name, price, and release_date. It has eight records.

setupdata.sql

create table categories (
category_id  INT,
category_name VARCHAR(40)
);

create table products (
product_id INT,
category_id  INT,
product_name VARCHAR(40),
price	DECIMAL(5,2)  ,
release_date DATE
);

insert into categories ( category_id  , category_name ) values(1, 'Mobile');
insert into categories ( category_id  , category_name ) values(2, 'Headphone');
insert into categories ( category_id  , category_name ) values(3, 'Tablet');
insert into products ( product_id , category_id  , product_name, price, release_date ) values(1027,2, 'Headphone 700',87.99, '5/13/2019');
insert into products ( product_id , category_id  , product_name, price, release_date ) values(1028,2, 'Headphone 450BT', 97.99, '2/4/2020');
insert into products ( product_id , category_id  , product_name, price, release_date ) values(1029,2, 'HD 1000XM3', 107.99, '8/5/2018');
insert into products ( product_id , category_id  , product_name, price, release_date ) values(1030,2, 'HD SoundES18',117.99,  '1/1/2017');
insert into products ( product_id , category_id  , product_name, price, release_date ) values(1021,1, 'APPLE 700',400.99,  '9/20/2019');
insert into products ( product_id , category_id  , product_name, price, release_date ) values(1022,1, 'Samsung 5',357.99,  '8/23/2019');
insert into products ( product_id , category_id  , product_name, price, release_date ) values(1035,3, 'GalatS6',187.99,  '10/13/2019');
insert into products ( product_id , category_id  , product_name, price, release_date ) values(1036,3, 'MS', 250.99, '6/13/2017');

Verify the data with a Select statement

SELECT category_id,
       count(*) totalProductCount
FROM   products
GROUP  BY category_id 

Query Results

As the results show, only the category_id = 2 has more than 2 products.

4. SQL Having Clause

4.1 HAVING Clause Without Group By

In this step, I will use a HAVING clause to find the minimum and maximum prices from the products table when the minimum and maximum prices are different.

HAVING min(price) != Max(price)

SELECT Min(price) min,
       Max(price) max
FROM   products
HAVING Min(price) != Max(price); 

Query Results

Note: when a HAVING clause is used without a GROUP BY clause, then the aggregate function is applied to the entire table. In this case, it returns 1 row.

The next query returns no data.

HAVING Min(price) = Max(price)

SELECT Min(price) min,
       Max(price) max
FROM   products
HAVING Min(price) = Max(price); 

No data was returned

4.2 GROUP BY Category HAVING Count Greater Than 2

In this step, I will use a HAVING clause with a GROUP BY clause to list the minimum and maximum prices for each category.

HAVING after GROUP BY

SELECT category_id,
       Min(price) min,
       Max(price) max
FROM   products
GROUP  BY category_id
HAVING Min(price) != Max(price); 

Query Results

The next query uses a HAVING clause with a GROUP BY clause which filters the categories with less than 2 products.

HAVING count(*> 2

SELECT   category_id,
         Sum(price) totalProductPrice
FROM     products
GROUP BY category_id
HAVING   Count(*) >2;

Query Results

4.3 Group By Category Having Sum Greater Than 300

In this step, I will demonstrate two ways to use a HAVING clause with a joined table.

HAVING Sum(price) > 300

SELECT   c.category_id,
         c.category_name ,
         Sum(price) totalProductPrice
FROM     products p,
         categories c
WHERE    p.category_id = c.category_id
GROUP BY c.category_id,
         c.category_name
HAVING   Sum(price) >300;

Query Results

In th next query, the joined clause is applied to the filtered query results.

Having in a subQuery

SELECT *
FROM   categories c,
       (SELECT category_id,
               Sum(price) totalProductPrice
        FROM   products
        GROUP  BY category_id
        HAVING Count(*) > 2) filterdV
WHERE  c.category_id = filterdV.category_id; 

Query Results

4.4 Group by Category HAVING Sum between 300 and 500

In this step, I will show two queries with more than one condition used in the HAVING clause.

HAVING with 2 Conditions

SELECT   c.category_id,
         c.category_name ,
         Sum(price) totalProductPrice
FROM     products p,
         categories c
WHERE    p.category_id = c.category_id
GROUP BY c.category_id,
         c.category_name
HAVING   Sum(price) >300 and sum(price) < 500;

Query Results

The next query shows categories with more than 2 products and a total price of more than 300.

HAVING with 2 Conditions -2

SELECT   c.category_id,
         c.category_name ,
         Sum(price) totalProductPrice
FROM     products p,
         categories c
WHERE    p.category_id = c.category_id
GROUP BY c.category_id,
         c.category_name
HAVING   Sum(price) >300 and count(*) > 2

Query Results

4.5 Having Avg and Order By Avg

In this step, I will show how to find the categories whose average product price is more than 100, and the query results are ordered by the average product price.

Having Average With Order By

SELECT   c.category_id,
         c.category_name ,
         avg(price) avgPrice
FROM     products p,
         categories c
WHERE    p.category_id = c.category_id
GROUP BY c.category_id,
         c.category_name
HAVING   avg(price) > 100 
order by avg(price);

Query Results

5. Summary

The HAVING clause is used in a SELECT statement which specifies conditions to filter which group results appear in the results. The conditions are the Boolean type which can be used with logical operators: AND and OR. In this example, I demonstrated the following usages of a Having clause:

• Having min(price) ! = max(price) clause without a Group by clause.
• Group by category and filtering with Having count(product) > 2.
• Group by category and filter with Having sum(price) > 300.
• Group by category and filter with Having avg(price) > 100, then Order By avg(price).

6. Download the Source Code

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.