Having Clause:




The HAVING clause is just like the WHERE clause. Both the HAVING and the WHERE clauses are used to display the results that are based on certain specific condition.

The only difference between the HAVING clause and the WHERE clause is that the HAVING clause is concerned with groups and the WHERE clause is concerned with the individual rows.




The having clause can receive inputs from the FROM, WHERE, and the GROUP BY clause. The following points should be kept in mind:

  • If the user specifies the GROUP BY clause, then the HAVING clause must be applied to the group created by the GROUP BY clause.
  • If the user specifies the WHERE clause but does not specify the GROUP BY clause then HAVING clause should be applied to the output obtained by the WHERE clause and that output should be treated as one group.
  • If the user does not specify a WHERE clause and the GROUP BY clause then the HAVING clause should be applied to the output that is obtained by the FROM clause and this output should be treated as one single group.




SYNTAX

SELECT column-name, function (column-name)

FROM table-name

WHERE column-name condition

Group by column-name

HAVING function (column-name) condition

EXAMPLE of HAVING CLAUSE:

Suppose you have a table that is named STUDENT as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97




Let suppose we are searching for the student whose STUD_ID that is student id is more than 150. Then the query will be as follows:

SELECT *

FROM student group by name

Having count (STUD_ID) > 150

The above query will generate the following result:

STUD_ID NAME
190

Abraham