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