HAVING Clause in SQL Server

HAVING Clause is used to restrict or limit the number of rows returned by a query.

HAVING Clause is used only with the SELECT Command. It is generally used along with the GROUP BY Clause.

Having Clause filter the resultt set that is obtained by GROUP BY Command.

It generally works on aggregate function. One important point to be noted about HAVING Clause is it will not work with text data type. HAVING Clause is mostly used to operate on int,float,money and similar data types.

Syntax for the HAVING clause is:

HAVING < search criteria>

SELECT <Column list> FROM <Table Name> GROUP BY < Column List> HAVING <search condition>

Example: Lets assume that we have a table with student marks details along with the subjects they have chosen.

we can use HAVING clause to find out the average aggregate marks in a particular subject and the subject which is having average aggregate marks less than 60( any number).

Data in the STUDENTS table is:





















Query:
  • SELECT SUBJECT,AVG(MARKS) FROM STUDENTS GROUP BY SUBJECT
Above query will find out the average marks in each subject.

Result set for the above query is:












It contains all the subjects and corresponding average aggregate marks.

To find out the subjects which are having average aggregate marks less than 60 can be find out by using the below query:

Query:

SELECT SUBJECT,AVG(MARKS) FROM STUDENTS GROUP BY SUBJECT HAVING AVG(MARKS)  <60

Result Set of the above query is:
This indicates the list of subjects which are having aggregate less than 60.


Note: HAVING clause without GROUP BY clause acts as WHERE clause.




This entry was posted in . Bookmark the permalink.

Leave a reply