How to Use HAVING With Aggregate Functions in SQL?

SQL provides many built-in functions to perform tasks. There are 4 types of functions Date functions, Char functions, Numeric functions, and Aggregate functions. 

Here we will be looking at aggregate functions and how to use them with the HAVING keyword.

Aggregate Functions are database built-in functions that act on multiple rows of the table and produce a single output. There are basically 5 aggregate functions that we use frequently in SQL. Aggregate functions are deterministic. Common aggregate functions are as follows:

• COUNT(): Calculates the total number of rows in the table, it returns a single value.
• AVG(): Calculates the average of the values to the column it is applied to.
• MIN(): Returns the minimum value in the column it is applied to.
• MAX(): Returns the maximum value in the column it is applied to.
• SUM(): Return the sum of all values of the column it is applied to.

When to use the HAVING keyword?

WHERE keyword that we used to filter data on the given condition works well with SQL operators like arithmetic operator, comparison operator, etc but when it comes to aggregate functions we use the HAVING keyword to sort data on the given condition. The GROUP BY clause is also used with the HAVING keyword.

Syntax:

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

To use SUM() with Having clause:

Step 1: Create a database

Query:

CREATE DATABASE database_name;

Step 2: Create a table named products.

Query:

CREATE TABLE PRODUCTS(product_id int primary key, product_name varchar(45), product_cost float);

Step 3: Insert values in the table

Query:

INSERT INTO PRODUCTS VALUES 
(1001, 'Colgate Toothpaste', 2.25), (1002 'T-Shirt', 5), 
(1003, 'Jeans', 6.5), (1004, 'Shorts', 4.5), 
(1005, 'Sneakers', 8.99), (1007, 'Mouthwash', 3.35), 
(1008, 'Denim Jeans', 8.99), (1009, 'Synsodyne Toothpaste', 3.35);

Step 4: Now let’s see the contents of the products table.

Query:

SELECT * FROM products;

Output:

Step 5: Now our task is to print all those products whose sum of product cost is greater than 3.50.

Query:

SELECT product_name, product_cost  
FROM products  
GROUP BY product_name, product_cost  
HAVING SUM(product_cost) > 3.5  
ORDER BY product_cost;

Output:

PRODUCTS TABLE

Here only those products are displayed whose cost is greater than 3.5 

To use MAX() and MIN() with Having clause

We are using the same products table that we used in the previous example.

Our task is to find the products name whose maximum price is greater than 7 and those products names whose minimum price is less than 3.

Query:

SELECT * FROM products;

QUERY 1(To find products with a maximum price greater than 7)

SELECT product_name 
FROM products 
GROUP BY product_name 
HAVING MAX(product_cost) > 7;

OUTPUT

QUERY 2(To find products with a minimum price less than 3)

SELECT product_name 
FROM products 
GROUP BY product_name 
HAVING MIN(product_cost) < 3;

Output:

 To use AVG() with Having clause

We will be using the products table to demonstrate this part.

Query:

SELECT * FROM products;

Now, we want to select those products whose price is greater than the average price of the products table.

Query:

SELECT product_name
FROM products
GROUP BY product_name
HAVING AVG(product_cost) > (SELECT AVG(product_cost) FROM products);

Output:

Here only those products are present whose average price is greater than the average price of the products table.

To use Count() with Having clause

Step 1: We will create a database.

Query:

CREATE DATABASE database_name;

Step 2: Create table login. 

Query:

CREATE TABLE login(signin_id int PRIMARY KEY ,customer_id int, date_login date);

Step 3:  Insert values in the table. 

Query:

INSERT INTO login values
(1, 121, '2021-10-21'), (2, 135, '2021-05-25'),  
(3, 314, '2021-03-13'), (4, 245, '2021-07-19'),  
(5, 672, '2021-09-23'), (6, 135, '2021-06-12'),  
(7,120,'2021-06-14'), (8, 121, '2021-04-24'),  
(9,135, '2021-06-15'), (10, 984, '2021-01-30');

Step 4: Display the content of the table.

Query:

SELECT * FROM login;

Output:

Now we want to display those customer ids (s) that occurred at least 2 times.

Query:

SELECT customer_id  
FROM login
 GROUP BY customer_id 
HAVING COUNT(customer_id) >=2 ;

Output:

Here customer_id 121 and 135 occurred at least 2 times.