SQLite HAVING Clause

Here we will learn SQLite Having clause with example and how to use SQLite having clause with a group by, where in select statements with example.

SQLite HAVING Clause

Functionally in SQLite HAVING clause is identical to the WHERE clause. The SQLite HAVING clause is a further condition applied after aggregation takes place along with a group by in select statement.

 

Generally in the 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 a set of groups and the HAVING clause will apply filters on groups based on specified conditions.

Syntax of SQLite Having Clause

Following is the syntax of using the SQLite HAVING clause with a select statement.

 

SELECT result

FROM [table-list]

HAVING [expr]

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 a result. 
  • table-list - It may be the list of a table from which you want results. 
  • expr - This is similar to WHERE except that HAVING applies after grouping has occurred.

SQLite Having Clause Example

To see how to use SQLite Having clause in a select statement, first create a vegetable table and insert some data by running the following queries.

 

CREATE TABLE vegetable(

name TEXT,

color TEXT);

 

INSERT INTO vegetable

values ('peas','green'),

('carrot','orange'),

('cucumber','green');

Let’s look at records of vegetable table by running following query.

 

sqlite> SELECT * FROM vegetable

 

name        color

----------  ----------

peas        green

carrot      orange

cucumber    green

If we want to get color names where it exists more than once in the vegetable table, we need to write a query like as shown below.

 

SELECT color, count(*)

FROM vegetable

GROUP BY color

HAVING count(*)>1;

In the above SQLite query, we used Group By with Having clause to get color details where it exists more than one in a vegetable table.  The output of the above statement is as follows:

 

color      count(*)

---------- ----------

green      2

If you don’t need to show the count, as a result, then remove count(*) from a query like as shown in the following example.

 

SELECT color

FROM vegetable

GROUP BY color

HAVING count(*) >1;

Following is the result of the above SQLite having clause example.

 

color            

----------

green       

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.

Previous
 
Next