SQL HAVING CLAUSE

<<Previous - SQL GROUP BY

Next - SQL AND OPERATOR >>

• In SQL, the HAVING clause is used to apply the conditions for filtering the result of the GROUP BY clause.
• The HAVING clause is always used in the SELECT statement in SQL.
• This HAVING clause must always follow the GROUP BY clause in the SQL SELECT statement.
• The HAVING clause can work with aggregate functions.
• The HAVING clause is similar to the WHERE clause, but the WHERE clause does not work with aggregate functions.
• The WHERE clause can apply the conditions on the selected column from the table, whereas the HAVING clause applies the conditions on the groups defined by the GROUP BY clause.
• If you want to use the ORDER BY clause in the query, that it is to be placed after the HAVING clase.

SQL HAVING Clause Syntax

Following is the basic syntax of HAVING clause in SELECT statement.

	SELECT column-name1,column-name2
	FROM Table-name
	WHERE [conditions]
	GROUP BY column-name
	HAVING [conditions]
	ORDER BY column-name;

HAVING Clause - Examples

• Example 1:

  Following is the Student_Personal table in the collegedb database.

  
  	mysql> SELECT * From Student_Personal;
  
  	+--------+---------+------+-----------+
  	| RollNo | Name    | Age  | City      |
  	+--------+---------+------+-----------+
  	|      1 | Aruna   |   18 | Chennai   |
  	|      2 | Varun   |   19 | Bangalore |
  	|      3 | Ara     |   19 | Kerala    |
  	|      4 | Markdin |   18 | Mumbai    |
  	|      5 | Kannan  |   20 | Kerala    |
  	|      6 | Kanika  |   18 | Chennai   |
  	|      7 | Jose    |   19 | Kerala    |
  	|     10 | Sreeja  |   20 | Bangalore |
  	+--------+---------+------+-----------+
  	8 rows in set (0.00 sec)
  
  

  Following is the example query to count the number of student in the particular age group.

  
  	mysql>  SELECT COUNT(Rollno),Age
  		FROM Student_Personal
  		GROUP BY Age;
  
  	+---------------+------+
  	| COUNT(Rollno) | Age  |
  	+---------------+------+
  	|             3 |   18 |
  	|             3 |   19 |
  	|             2 |   20 |
  	+---------------+------+
  	3 rows in set (0.10 sec)
  
  

  Now, we want to count at least 3 students from the particular age group. In this case, we will use the HAVING clause to check the condition as Count(RollNo) >= 3

  
  	mysql>  SELECT COUNT(Rollno),Age
  		FROM Student_Personal
  		GROUP BY Age
  		HAVING COUNT(RollNo) >= 3;
  
  	+---------------+------+
  	| COUNT(Rollno) | Age  |
  	+---------------+------+
  	|             3 |   18 |
  	|             3 |   19 |
  	+---------------+------+
  	2 rows in set (0.10 sec)
  
  

• Example 2:

  Consider the below Staff_Table in the collegedb database.

  
  	mysql> SELECT * From Staff_Table;
  
  	+-------+-----------+-------+--------+--------+
  	| ID_NO | Name      | Dept  | Salary | Job_ID |
  	+-------+-----------+-------+--------+--------+
  	|   101 | Madhan    | IT    |  20000 |    102 |
  	|   102 | Kamini    | IT    |  25000 |   NULL |
  	|   103 | Senthil   | CSE   |  30000 |    104 |
  	|   104 | Vani      | CSE   |  30000 |   NULL |
  	|   105 | Aadhi     | MECH  |  35000 |   NULL |
  	|   106 | Arun      | CIVIL |  35000 |   NULL |
  	|   107 | Vasnthi   | CIVIL |  28000 |    106 |
  	|   108 | Balan     | CIVIL |  27500 |    106 |
  	|   109 | Devid     | MECH  |  23500 |    105 |
  	|   110 | Aruna     | ADMIN |  15000 |    113 |
  	|   111 | Boomi     | ADMIN |  17000 |    113 |
  	|   112 | Kathirvel | ADMIN |  17500 |    113 |
  	|   113 | Kathir    | ADMIN |  22000 |   NULL |
  	+-------+-----------+-------+--------+--------+
  	13 rows in set (0.01 sec)
  

  Following is an example query to sum up the salary for the different department .

  
  	mysql> SELECT ID_No, Name, Dept, sum(Salary) as Salary
  		From Staff_Table
  		GROUP BY Dept;
  
  	+-------+---------+-------+-------------+
  	| ID_No | Name    | Dept  |   Salary    |
  	+-------+---------+-------+-------------+
  	|   101 | Madhan  | IT    |       45000 |
  	|   103 | Senthil | CSE   |       60000 |
  	|   105 | Aadhi   | MECH  |       58500 |
  	|   106 | Arun    | CIVIL |       90500 |
  	|   110 | Aruna   | ADMIN |       71500 |
  	+-------+---------+-------+-------------+
  	5 rows in set (0.00 sec)
  

  Now, we want to display the department whose salary is greater than or equal to 50,000.

  
  	mysql> SELECT ID_No, Name, Dept, sum(Salary) as Salary
  		From Staff_Table
  		GROUP BY Dept
  		HAVING sum(Salary) >= 50000
  		ORDER BY ID_NO;
  
  	+-------+---------+-------+-------------+
  	| ID_No | Name    | Dept  |    Salary   |
  	+ -------+---------+-------+-------------+
  	|   103 | Senthil | CSE   |       60000 |
  	|   105 | Aadhi   | MECH  |       58500 |
  	|   106 | Arun    | CIVIL |       90500 |
  	|   110 | Aruna   | ADMIN |       71500 |
  	+-------+---------+-------+-------------+
  	4 rows in set (0.01 sec)
  
  

<<Previous - SQL GROUP BY

Next - SQL AND OPERATOR >>