Introducing Radical.sh

Forget Code launches a powerful code generator for building API's

GROUP BY in Oracle

(1) All the dependent columns or columns used in GROUP BY function must form the basis of grouping, hence must be included in GROUP BY clause also.
(2) GROUP BY clause does not support the use of column alias, but the actual names.
(3) GROUP BY clause can only be used with aggregate functions like SUM, AVG, COUNT, MAX, and MIN.
(4) Aggregate functions cannot be used in a GROUP BY clause.

Below query lists the count of employees working in each department.

SELECT DEPARTMENT_ID,  COUNT (*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;


Similarly, below query to find sum of salaries for respective job ids in each department. Note the group is established based on Department and Job id. So they appear in GROUP BY clause.

SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID;


The below query also produces the same result. Please note that grouping is based on the department id and job id columns but not used for display purpose.

SELECT SUM (SALARY)
FROM EMPLOYEES
GROUP BY 
DEPARTMENT_ID, JOB_ID;


A WHERE clause is used to filter rows BEFORE the GROUPING action (i.e., before the calculation of the aggregate functions).
A HAVING clause filters rows AFTER the GROUPING action (i.e., after the calculation of the aggregate functions).

SELECT JOB_ID,    SUM (SALARY)
FROM EMPLOYEES
WHERE JOB_ID > 100
GROUP BY JOB_ID
HAVING SUM (SALARY) > 10000;


The HAVING clause is a conditional option.