SQLite HAVING Clause

  Total Views : 66
  Comments : 0
 
  Keywords : SQLite group by having clause with example, How to use sqlite having clause with group by example, Use sqlite having clause with select statement example

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.

SQLite HAVING Clause

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.

Syntax of SQLite Having Clause

Following is the syntax of using SQLite HAVING clause with 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 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.

SQLite Having Clause Example

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(

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 one in vegetables table, we need to write a query like as shown below.

 

SELECT color, count(*)

FROM vegetable

GROUP BY color

HAVING count(*)>1;

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:

 

color      count(*)

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

green      2

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

 

SELECT color

FROM vegetable

GROUP BY color

HAVING count(*) >1;

Following is the result of 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.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User