HAVING CLAUSE

HAVING CLAUSE:

This clause is used to evaluate a condition with group by clause. Generally for evaluating conditions we will use WHERE clause, but where clause does not support by group by clause.

Syntax:

SELECT STATEMENT GROUP BY COLUMN NAME [HAVING CONDITION]

E.g.: SELECT DEPTNO, MAX (SAL) AS HISAL, MIN (SAL) AS LOSAL,

SUM (SAL) AS TOTSAL, AVG (SAL) AS AVGSAL FROM EMP

GROUP BY DEPTNO HAVING AVG (SAL)>10000

DEPTNO                HISAL                   LOSAL      TOTSAL        AVGSAL

20                                20000                  5000            25000              12500

SPECIAL FUNCTIONS

COUNT( ): Count function counts the number of rows available for a specific column. It counts only values except NULL values. It is associated with three parameters.

COUNT (*)

COUNT (Column Name)                  

E.g.: SELECT COUNT (*) AS NO_OF_ROWS FROM EMP

NO_OF_ROWS

            5

E.g.: SELECT COUNT (DEPTNO) AS NO_OF_ROWS FROM EMP

NO_OF_ROWS

            5

DISTINCT ( ): This function displays the different values available for a specific column. It considers one NULL value.

E.g.: SELECT DISTINCT (DEPTNO) AS DIFF_VALUES FROM EMP

DIFF_VALUES

            10

            20       

           

E.g.: SELECT COUNT (DISTINCT( DEPTNO)) AS NO_OF_ROWS FROM EMP

NO_OF_ROWS

            2

Note: COUNT function does not count the rows of the column whose data type is BIGINT, In such situations we have to use COUNT_BIG function