SQLite HAVING Clause

“SQLite is a framework that defines a self-contained transaction-oriented SQL database system and requires no deployment. In SQLite, the HAVING command of the SELECT query is optional. The HAVING command provides a query requirement for the column. The HAVING clause is typically used in conjunction with the GROUP BY command. The GROUP BY statement aggregates a group of columns into summarized fields or rows. The HAVING command then selects rows according to a defined requirement. We must incorporate the GROUP BY statement whenever we employ the HAVING clause; otherwise, we would obtain the error.

We will discuss using the HAVING statement of the SQLite query to define a filtered requirement for any column of the specified table in this tutorial.

Any compiler will be installed to run the queries. We installed the BD Browser for SQLite software here. First, we selected the “New database” option from the context menu and established a new database. It would be placed in the SQLite database files folder. We run the query to form a new database. Then, using the specialized query, we’ll construct a table.”

Use CREATE Query

In this article, we will create the “Nurse” table using the CREATE query. We have specified the data of different nurses in it. The table “Nurse” has different attributes “Id,” “First_name,” “last-name,” “Gender,” “Age,” and “Salary.” The column “Id” has an integer data type, “first_name,” “last_name,” and “gender” has a text data type, and “Salary” has a numeric data type.

CREATE TABLE Nurse (

Id INTEGER PRIMARY KEY,

First_name TEXT,

last_name TEXT,

Gender TEXT,

Age NUMERIC,

Salary NUMERIC

);

The output shows that the query “CREATE” is successfully executed.

Use INSERT Query

Now we want to insert the data into the table “Nurse,” so we execute the query of “INSERT.”

INSERT INTO Nurse (Id, First_name, last_name, Gender, Age, Salary)

VALUES (978, 'Muqadas', 'Ameen', 'Female', '38', '25000'),

(490, 'Farah', 'Javaid', 'Female', '32', '20000'),

(233, 'Sarah', 'Muddasir', 'Female', '35', '25000'),

(571, 'Saim', 'Munawar', 'Male', '28', '18000'),

(864, 'Hassan', 'Asghar', 'Male', '40', '30000');

In the table, we successfully inserted the data, including Id, first_name, last_name, gender, age, and salary of different nurses.

Use “SELECT” Query

We can fetch the entire data of the table “Nurse” by applying the “SELECT” query. The FROM clause in the query shows the table’s name from where we will obtain the data.

>> SELECT * FROM Nurse;

After running the above-mentioned query, we can get all data of the 5 nurses.

Use the HAVING Clause

After creating and inserting the data into the table, we are going to utilize the HAVING clause in the SELECT query.

In SQLite, the WHERE statement is being used to apply a requirement to particular table columns, while the HAVING statement is being applied to add specific requirements depending on the rows followed by the GROUP BY statement.

>> SELECT First_name, Gender, Age FROM Nurse GROUP BY Age HAVING Gender = 'Male';

In the resultant table, we acquire the data of nurses where gender is male because we specify the attribute Gender to the HAVING clause.

Use the COUNT Method

Let’s take a look at the utilization of SQLite’s HAVING statement along with the count method.

We are going to utilize the COUNT method along with the HAVING clause. The column “Id” is provided as a parameter to the COUNT function. We apply the GROUP BY and HAVING clause both on Id. The count method might be applied to get the nurses’ ID and first names. The HAVING command limits the entries to return data of only nurses having the Id 864 in the table.

>> SELECT Id, First_name, COUNT(Id) FROM Nurse GROUP BY Id HAVING Id = 864;

We get the record of that nurse whose id is 864 in the outcome.

In this step, the COUNT function will be employed on the column “First_name.” Then, the HAVING clause is applied to this function. Further, we apply the condition on the HAVING clause in such a way that COUNT (First_name) < 2.

>> SELECT * FROM Nurse GROUP BY First_name HAVING count(First_name) < 2;

The output of the above query is shown in the figure below:

The COUNT function is used on the column “Gender” in this phase. The HAVING clause, therefore, provides this function. We also set a condition to the HAVING clause.

>> SELECT * FROM Nurse GROUP BY Gender HAVING count(Gender) > 2;

We obtain the data of that nurse who satisfies the condition mentioned in the HAVING clause.

Use the MIN Method

Next, we’ll examine using SQLite’s HAVING statement and the minimum method.

The minimum method would perhaps also be employed to retrieve the id, first name, and a minimum income of every nurse. The HAVING command will only provide nurses’ records with a minimum pay significantly lower than 25,000.

>> SELECT Id, First_name, min(salary) AS "Lowest salary" FROM Nurse

GROUP BY Id HAVING MIN(salary) < 25000

In this instance, we created a new column named “Lowest salary” and gave salaries to nurses whose salaries were less than 25,000.

Use the MAX Method

We will utilize the max method to get the Id, first name, and the nurses’ maximum earnings. The HAVING command will only provide information for nurses with a maximum income of 30,000 or above.

>> SELECT Id, First_name, max(salary) AS "Maximum salary" FROM Nurse

GROUP BY Id HAVING MAX(salary) >= 30000;

The unique column called “Maximum salary” is created in the resultant table. It shows the list of the incomes of nurses earning greater than 30,000.

Use BETWEEN Clause

We can also specify the condition of the HAVING clause by using BETWEEN. We employ the aggregate method within the HAVING statement to identify records of nurses between the ages of 28 and 35, as demonstrated in the subsequent query:

>> SELECT * FROM Nurse GROUP BY Id HAVING Age BETWEEN 28 AND 35;

The output shows the data of three nurses because the age of only these nurses lies between 28 and 35.

Conclusion

In this article, we have explored how to employ the SQLite HAVING statement in SELECT queries. The HAVING command in SQLite is similar to the WHERE command of the SELECT query. The HAVING clause is an additional statement employed after accumulation and a GROUP BY clause in a SELECT query. If we don’t employ the HAVING command with the GROUP BY command, this will behave like a WHERE statement. The GROUP BY command in SQLite organizes columns into groups, and the HAVING statement applies limits to those columns based on provided parameters. With the help of multiple examples, we applied the HAVING clause with additional functions such as COUNT, MIN, and MAX techniques.