Example of HAVING Clause
For better understanding, we are going to use the COMPANY table and the table consists of five columns and they are ID, NAME, AGE, ADDRESS and SALARY. If you don’t know How to Create a Table in SQLite then refer this.
After inserting some data into the table, Our table looks like as below:
Example 1: Simple HAVING Clause
SELECT ID, Age
FROM Company
GROUP BY ID
HAVING age>35;
Output:
Explanation: Here we are going to fetch the ID and Age from the company table and we are going to group by ID column and applying HAVING Clause on the age which is greater than 35.
Example 2: Using SUM Function
We are going to use the SQLite HAVING Clause with the SUM() function. SUM fuction is used to return the summation values of the expression.
Syntax:
SELECT expression1, expression2, ... expression_n, sum(aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
HAVING condition;
Query:
SELECT ID, sum(salary) as Maxsal
FROM Company
GROUP BY ID
HAVING sum(salary)>50000;
Output:
Explanation: In the above Query, we have fetch the ID and sum of salary from the company table and we are going to group by ID and only those sum of the salaries are fetched that are greater than 50000. As you can see that 3 rows are fetched as they met the HAVING Clause condition as sum of salary greater than 50000.
Example 3: Using Count Function
Now let us try to use the COUNT() function with the HAVING Clause. HAVING Clause with the count function is used to count the specified column.
Syntax:
SELECT expression1, expression2, ... expression_n, count(aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
HAVING Condition;
Query:
SELECT salary, count(name) as no_of_employees
FROM Company
GROUP BY salary
HAVING salary > 45000;
Output:
Explanation: In the above Query, we have fetch the name, sum of salary from company table where salary is greater than 4000 and we are applying group by Clause on the name and HAVING Clause on the sum of salary that is greater than 45000. As you can see that the sum of salary greater than 45000 are fetched along with the count of employees.
Example 4: Using MIN Function
Now let us try to use SQLite Min function with the HAVING Clause. It is used to calculate the smallest value of the expression.
Syntax:
SELECT expression1, expression2, ... expression_n, Min(aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
HAVING Condition;
Query:
SELECT ID, min(salary) as Minsal
FROM Company
GROUP BY ID
HAVING min(salary)<50000;
Output:
Explanation: In the above Query, we have fetch the ID and minimum salary from company table and we are going to group by ID column where minimum salary is less than 50000. As you can see that 3 rows are fetched as they met the specified condition.
Example 5: Using MAX Function
Now let us try to use SQLite Max function with the HAVING Clause. It is used to calculate the smallest value of the expression.
Syntax:
SELECT expression1, expression2, ... expression_n, Max(aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
Having Condition;
Query:
SELECT ID, max(salary) as Maxsal
FROM Company
GROUP BY ID
HAVING max(salary)>50000;
Output:
Explanation: In the above Query, we have fetch the ID and maximum salary from company table and we are going to group by ID column where maximum salary is greater than 50000. As you can see that 3 rows are fetched as they met the specified condition.
SQLite HAVING Clause
SQLite is a server-less database engine and it is written in c programming language. The main moto for developing SQLite is to escape from using complex database engines like MySQL etc. It has become one of the most popular database engines as we use it in Television, Mobile Phones, web browsers, and many more. It is written simply so that it can be embedded into other applications. In this article, we will be learning about SQLite HAVING Clauses, how it works, and its functionality.