Oracle GROUP BY Clause

➔ In Oracle SQL, the GROUP BY clause is used to create a summary row by combining rows that have the same value in a specific column.

➔ It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN() to perform calculations on each group.

➔ The optional GROUP BY clause comes after the WHERE clause (which filters rows) and before the optional HAVING clause (which filters groups).

Oracle GROUP BY feature.

➔ Any column in the SELECT list that is not part of an aggregate function must be included in the GROUP BY clause.

➔ All NULL values ​​in the data set are treated as a single, equivalent group.

Basic Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

Find the number of employees in each department and group them by department_id.

Example:

SELECT department_id, COUNT(*) AS "Total Employees"
FROM hr.employees
GROUP BY department_id;

Perform multiple aggregate calculations, such as determining the average and total salary for each position.

Example:

SELECT job_id, 
       AVG(salary) AS "Average Salary", 
       SUM(salary) AS "Total Salary"
FROM hr.employees
GROUP BY job_id;

We can group by multiple columns to get more granular data, such as counting the number of employees by department_id and job_id.

Example:(Multiple columns)

SELECT department_id, job_id, COUNT(*)
FROM hr.employees
GROUP BY department_id, job_id
ORDER BY department_id;

Filtering Groups with WHERE ( WHERE filters individual rows before grouping)

Example:(Filter by WHERE clause)

SELECT department_id, AVG(salary)
FROM hr.employees
WHERE department_id > 20
GROUP BY department_id;

Filtering Groups with HAVING (HAVING filters the aggregated data after grouping)

Example:(Filter by HAVING clause)

SELECT department_id, AVG(salary)
FROM hr.employees
GROUP BY department_id
HAVING AVG(salary) > 6500;

Filtering Groups with both WHERE and HAVING

Example:(Filter by both WHERE and HAVING clause)

SELECT department_id, AVG(salary)
FROM hr.employees
WHERE department_id>20
GROUP BY department_id
HAVING AVG(salary) > 6500;

Oracle ROLLUP extension in GROUP BY clause

➔ The Oracle ROLLUP extension enhances the GROUP BY clause, allowing it to automatically generate multiple levels of subtotals and a grand total within a single result set.

➔ The ROLLUP operator creates different levels of aggregation by processing columns from left to right.

Example (with ROLLUP)

SELECT department_id, job_id, SUM(salary) AS total_salary
FROM hr.employees
GROUP BY ROLLUP (department_id, job_id)
ORDER BY department_id, job_id;

Explanation

Displays the total salary for each distinct combination of department_id and job_id.

Subtotal: For each department_id, this returns a row where job has the value NULL, indicating the total salary for that entire department_id.

Grand Total: The very last row (where department_id and job_id are both NULL) represents the total salary across all department_ids.

Example (without ROLLUP)

SELECT department_id, job_id, SUM(salary) AS total_salary
FROM hr.employees
GROUP BY (department_id, job_id)
ORDER BY department_id, job_id;

Written (Syntax) Order:

➔ SELECT [DISTINCT] : Spcify what columns to show.

➔ FROM : Specify which tables to pull from.

➔ WHERE : Specify which rows to keep.

➔ GROUP BY : Specify how to aggregate them.

➔ HAVING : Specify which groups to keep.

➔ ORDER BY : Specify how to sort the final result.

Actual (Execution) Order:

➔ FROM : Identify source tables.

➔ WHERE : Filter rows based on conditions applied.

➔ GROUP BY : Group the remaining rows after filtering on WHERE clause.

➔ HAVING : Filter the resulting groups.

➔ SELECT : Select columns and evaluate aliases, expressions.

➔ DISTINCT : Remove duplicate rows from the selected result set.

➔ ORDER BY : Sort the final query result set for display.