Home » Core Java » sql » SQL HAVING Clause

About Mary Zheng

Avatar photo
Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.

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_idcategory_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

category_idtotalProductCount
12
24
32
Table 1 Category and Its Product Count

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

minmax
87.99400.99
Table 2 Minimum and Maximum Prices

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

minmax
Table 3 No Data Found

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

category_idminmax
1357.99400.99
287.99117.99
3187.99250.99
Table 4 List both Minimum and Maximum Prices for Each Category

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

category_idtotalProductPrice
2411.96
Table 5 List Category Total Product Price When It Has More Than 2 Products

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

category_idcategory_nametotalProductPrice
1Mobile758.98
2Headphone411.96
3Tablet438.98
Table 6 Category Total Product Prices

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

category_idcategory_namecategory_idtotalProductPrice
2Headphone2411.96
Table 7 Category’s Total Product Prices Filtering with More Than 2 Products

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

category_idcategory_nametotalProductPrice
2Headphone411.96
3Tablet438.98
Table 8 Category with Total Product Prices Between 300 And 500

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

category_idcategory_nametotalProductPrice
2Headphone411.96
Table 9 Category with More Than 2 Products and Total Price Greater Than 300

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

category_idcategory_nameavgPrice
2Headphone102.99
3Tablet219.49
1Mobile379.49
Table 10 Order by Average Price

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

Download
You can download the full source code of this example here: SQL HAVING Clause

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 ....

 

Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments