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.

SQLite HAVING CLAUSE

SQLite HAVING Clause is used with the combination of Group by clause. It is used to filter the groups that are formed by the Group by Clause based on the specified condition. It is mandatory to use the Group By Clause whenever the HAVING Clause is used and the HAVING Clause is applied to the result set of the Group by Clause to restrict the repeating rows.

Whenever you want to use the WHERE Clause then you need to use it before the Group By Clause followed by the HAVING Clause.

Syntax:

SELECT expression1, expression2, ... expression_n, aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
HAVING condition;

Explanation: We are using the select statement to fetch the data followed by the column_names and the aggregate function and the table name and where condition if required and if the condition is true, then group by condition is applied and on the result of it HAVING Clause is applied.

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:

Company Table

Example 1: Simple HAVING Clause

SELECT ID, Age
FROM Company
GROUP BY ID
HAVING age>35;

Output:

Having Age greater than 35

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:

Having clause with Sum Function

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:

Having clause with Count

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:

Having Clause with Min Function

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:

Having clause with Max Function

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.

Conclusion

Having Clause is used to filter the rows that are fetched by the Group by Clause. HAVING Clause is used with the aggregate functions like SUM, COUNT, MIN, MAX. In this article we have seen the HAVING Clause with all the aggregate functions and you can clearly understand by looking at the examples. HAVING Clause can used with the Group By Clause only as it specifies a search condition with the group by Clause.