GROUP BY CLAUSE: This clause
is used to divide the table into number of subgroups based on a specific
column.
Syntax: SELECT {COLUMNS LIST} FROM TABLENAME
GROUP
BY COLUMN NAME
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
DEPTNO HISAL LOSAL TOTSAL AVGSAL
10 15000
5000 30000 10000
20 20000
5000 25000 12500
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.
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
No comments:
Post a Comment