Here we will learn SQLite Having clause with example and how to use SQLite having clause with group by, where in select statements with example.
Functionally in SQLite HAVING clause is identical to WHERE clause. The SQLite HAVING clause is a further condition applied after aggregation takes place along with group by in select statement.
Generally in SQLite WHERE clause, which applies a condition to individual elements in a table and HAVING clause is used to add filter conditions based on the groups created by Group By clause.
Always we need to use SQLite HAVING clause with GROUP BY otherwise it will act as a WHERE clause. In SQLite the GROUP BY clause will group rows into set of groups and HAVING clause will apply filters on groups based on specified conditions.
Following is the syntax of using SQLite HAVING clause with select statement.
If you observe above SQLite Having statement, we defined multiple properties those are
result - It may be no. of column or expression that you want as result.
table-list - It may be list of table from which you want result.
expr - This is similar to WHERE except that HAVING applies after grouping has occurred.
To see how to use SQLite Having clause in select statement first create vegetables table and insert some data by running following queries.
CREATE TABLE vegetable(
INSERT INTO vegetable
Let’s look at records of vegetable table by running following query.
sqlite> SELECT * FROM vegetable
If we want to get color names where it exists more than one in vegetables table, we need to write a query like as shown below.
SELECT color, count(*)
GROUP BY color
In above sqlite query we used Group By with Having clause to get color details where it exists more than one in vegetables table. The output of above statement is as follows:
If you don’t need to show the count in result remove count(*) from query like as shown in following example.
GROUP BY color
HAVING count(*) >1;
Following is the result of above sqlite having clause example.
The main difference is that the WHERE clause can only reference expressions that do not contain aggregate functions, while the HAVING clause can reference any result column.