GROUP BY, ORDER BY, HAVING and Miscellaneous Functions in SQL

Read this Oracle Tutorial Point post to know when to use GROUP BY, ORDER BY, HAVING keywords and Miscellaneous functions in Oracle SQL. If you have any queries about this post, please comment on comments section.

Creating Groups of Data:
GROUP BY Clause:
The GROUP BY clause is used to decide the rows in a table into groups.

Syntax:

SELECT COLUMN, GRP_FUN (COLUMN)
 FROM TABLE
 WHERE CONDITION
 GROUP BY GRP_BY_EXPR
 ORDER BY COLUMN;

Conditions to use GROUP BY Clause:

1. If the GROUP function is included in a SELECT clause, we should not use individual results columns.
2. Those non GROUP functional columns should declare in GROUP BY clause.
3. Rows can be pre excluded before dividing them into GROUP by using WHERE clause.
4. Column aliases cannot be used in GROUP BY clause.
5. By default, rows are sorted by ascending order of the columns included in the GROUP BY list.
6. The column applied upon GROUP BY clause need not be part of SELECT list.

Example:

SQL> SELECT DNO, AVG(SAL) FROM EMP GROUPD BY DNO;
SQL> SELECT DNO, AVG(SAL) FROM EMP GROUPD BY DNO ORDER BY AVG(SAL);
SQL> SELECT DNO, MIN(SAL), MAX (SAL) FROM EMP GROUPD BY DNO;
SQL> SELECT DNO, JOB, SUM(SAL) FROM EMP GROUPD BY DNO,JOB;

The above specification falls under the principle of GROUP within GROUP’S.

SQL> SELECT DNO, MIN(SAL), MAX(SAL) FROM EMP WHERE JOB='CLERK' GROUP BY DNO;
SQL> SELECT DNO, SUM(SAL),AVG(SAL) FROM EMP WHERE JOB='CLERK' GROUP BY DNO;

Excluding Groups of Results:
HAVING Clause:
It is used to specify which groups are to be displayed.

Syntax:

SELECT COLUMN, GROUP_FUNCTION
 FROM TABLE
 [WHERE Condition]
 [GROUP BY Group_By_expr]
 [HAVING Group_Condition]
 [ORDER BY Column_Name/Alias];

What will do HAVING Clause?

1. Rows are Grouped
2. The Group function is applied to the group
3. The Groups that match the criteria in the HAVING Clause are displayed.
4. The HAVING clause can be precede GROUP BY clause. But, it is more logical to declare it before GROUP BY.
5. HAVING clause can be used without a GROUP function in the SELECT list.
6. If rows are restricted based on the result of a group function, we must have a GROUP BY clause as well as the HAVING Clause.

Example:

SQL> SELECT DNO,AVG(SAL) FROM EMP GROUP BY DNO HAVING MAX(SAL)>2000;
SQL> SELECT JOB, SUM(SAL) PAYROLL FROM EMP WHERE JOB NOT LIKE 'SALES%' 
     GROUP BY JOB HAVING SUM(SAL)>5000 ORDER BY SUM(SAL);
SQL> SELECT DNO, MIN(SAL),MAX(SAL) FROM EMP WHERE JOB='CLERK' 
     GROUP BY DNO HAVING MIN(SAL)<1000;
SQL> SELECT DNO,SUM(SAL) FROM EMP GROUP BY DNO HAVING COUNT(DNO) > 3;
SQL> SELECT DNO, AVG(SAL),SUM(SAL),MAX(SAL),MIN(SAL) FROM EMP 
     GROUP BY DNO HAVING COUNT(*)>3;

Nesting of Group Functions:
Group functions can be nested to a depth of two.

Example:

SQL> SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DNO;
SQL> SELECT MAX(SUM(SAL)),MIN(SUM(SAL)) FROM EMP WHERE GROUP BY DNO;
SQL> SELECT MAX(SUM(SAL)),MIN(SUM(SAL)) FROM EMP GROUP BY JOB;

Miscellaneous Functions:
GREATEST Function:
It returns the GREATEST of the list of exprs. All exprs after the first are implicitly converted to the data type of the first expr before the comparison. Oracle compares the exprs using non-padded comparison semantics. Character comparison is used on the value of the character in the data base character list.

Syntax: LEAST (expr1, expr2….)

Example:

SQL> SELECT LEAST('SUBBAIAH','SUBBAREDDY') FROM DUAL;
SQL> SELECT GREATEST(1000,2000,300) FROM DUAL;
SQL> SELECT GREATEST('20-JUN-16','01-OCT-16') FROM DUAL;

USER Function:
It returns the current Oracle USERS within the VARCHAR2 data type. The function cannot be used in the condition of the CHECK constraint.

Syntax: USER

Example:

SQL> SELECT USER FROM DUAL;

UID Function:
It returns an integer that uniquely identifies the current user.

Syntax: UID

Example:

SQL> SELECT UID FROM DUAL;
SQL> SELECT USER,UID FROM DUAL;

USERENV Function:
Returns information of VARCHAR2 data type above the current session.

Syntax: USERENV(Option)

The values in Options are,

1. ISDBA ==> Returns TRUE if DBA role is enabled.
2. LANGUAGE ==> Returns the LANGUAGE and territory used in the current session.
3. TERMINAL ==> Returns the OS identifier for the current session’s terminal.
4. SESSIONID ==> Returns the Auditing session identifier.
5. ENTRYID ==> Returns the available auditing entry identifier.
6. LANG ==> Returns the ISD abbreviation for the language name.
7. INSTANCE ==> Returns the instance identification number of the current instance.
8. CLIENT_INFO ==> Returns up to 64 bytes of User session information.

Example:

SQL> SELECT USERENV(‘LANGUAGE’) FROM DUAL;

VSIZE Function:
As per the below syntax if Expr is not NULL, this function returns the No of bytes in the internal representation of Expr. VSIZE function returns NULL when Expr is NULL.

Syntax: VSIZE(Expr)

Example:

SQL> SELECT ENAME,VSIZE(ENAME) FROM EMP;

SOUNDEX Function:
It returns a character string containing the phonetic representation of char. It allows comparison of words that are spelled differently. But, sounds like in English.

Syntax: SOUNDEX(CHAR)

Example:

SQL> SELECT ENAME FROM EMP WHERE SOUNDEX(ENAME)=SOUNDEX('SUBBAREDDY');
SQL> SELECT ENAME,JOB FROM EMP WHERE SOUNDEX(JOB)=SOUNDEX('SUBBAIAH');