HAVING Clause in SQLite

SQLite HAVING Clause To specify conditions for the groups created by the GROUP BY clause, the SQLite HAVING clause is used with the GROUP BY clause in the SELECT statement.

Syntax:

SELECT columns
FROM table_name  
WHERE conditions
GROUP BY columns
HAVING conditions 
ORDER BY columns

Example 1: TEACHERS Table:

ID	NAME	SALARY	SUBJECT
1	Jim	10000	Geology
2	John	20000	Geology
3	Watson	15000	Physics
4	Holmes	25000	Chemistry
5	Tony	30000	Physics

SELECT SUBJECT, SUM(SALARY) 
FROM TEACHERS 
GROUP BY SUBJECT
HAVING SUM(SALARY) &lt;= 30000;

Output:

SUBJECT	SUM SALARY
Geology	30000
Chemistry	25000

Explanation: In the above example, all the records are grouped by the SUBJECT Column where the sum of the SALARY column is less than or equal to 30000.

Example 2:

SELECT SUBJECT, SUM(SALARY) 
FROM TEACHERS 
GROUP BY SUBJECT
HAVING SUM(SALARY) &lt;= 30000   
ORDER BY SUBJECT ASC;

Output:

SUBJECT	SUM SALARY
Geology	30000
Chemistry	25000

Explanation: In the above example, all the records are grouped by the SUBJECT Column where the sum of the SALARY column is less than or equal to 30000. The result so obtained is then ordered in Ascending order by the SUBJECT Column.

Please follow and like us: