GROUP BY

The GROUP BY clause in Oracle SQL is used to group rows that have the same values in specified columns into summary rows, like calculating aggregate values (such as COUNT, SUM, AVG, MIN, and MAX) for each group. It is commonly used to perform analysis and aggregations on data based on one or more columns.

In this detailed guide, we’ll explore the core aspects of Oracle GROUP BY, including how it works, common use cases, syntax, and related functions.

 

1. What is the GROUP BY Clause?

The GROUP BY clause in SQL is used to arrange identical data into groups based on one or more columns. You can then perform aggregate operations on each of these groups.

Example:

SELECT department_id, COUNT(*) 

FROM employees 

GROUP BY department_id;

This query counts the number of employees in each department by grouping the rows based on the department_id column.

2. Syntax of GROUP BY

The basic syntax of the GROUP BY clause is as follows:

SELECT column1, column2, ..., aggregate_function(column)

FROM table_name

WHERE condition

GROUP BY column1, column2, ...;

• aggregate_function(column): This is an aggregate function (like COUNT(), SUM(), AVG(), etc.) applied to a column or set of columns to compute an aggregate value.
• column1, column2, ...: These are the columns by which to group the results.

3. Common Aggregate Functions with GROUP BY

The GROUP BY clause is typically used with aggregate functions, which summarize or analyze data for each group:

• COUNT(): Counts the number of rows in each group.
• SUM(): Calculates the sum of values in each group.
• AVG(): Calculates the average of values in each group.
• MIN(): Returns the minimum value in each group.
• MAX(): Returns the maximum value in each group.

Example:

SELECT department_id, COUNT(*) AS num_employees, AVG(salary) AS avg_salary

FROM employees

GROUP BY department_id;

This query counts the number of employees and calculates the average salary for each department.

4. GROUP BY with HAVING Clause

The HAVING clause is used to filter the results after the GROUP BY operation. While the WHERE clause is used to filter rows before grouping, HAVING filters after the grouping has been done.

Example:

SELECT department_id, AVG(salary) AS avg_salary

FROM employees

GROUP BY department_id

HAVING AVG(salary) > 50000;

This query calculates the average salary for each department and only returns those departments where the average salary is greater than 50,000.

5. GROUP BY with Multiple Columns

You can group by more than one column. When grouping by multiple columns, Oracle creates combinations of the column values to form the groups.

Example:

SELECT department_id, job_id, COUNT(*) 

FROM employees

GROUP BY department_id, job_id;

This query groups employees by both department_id and job_id, counting the number of employees in each combination of department and job.

6. Using GROUP BY with JOINs

The GROUP BY clause can also be used in queries that join multiple tables. In this case, the GROUP BY is applied to the result set from the join.

Example:

SELECT e.department_id, d.department_name, COUNT(*) 

FROM employees e

JOIN departments d ON e.department_id = d.department_id

GROUP BY e.department_id, d.department_name;

This query joins the employees table with the departments table and counts the number of employees in each department, grouping by both department_id and department_name.

7. GROUP BY with ROLLUP and CUBE

Oracle provides additional functionalities for generating subtotals and grand totals:

• ROLLUP: Generates summary rows for each level of grouping, providing subtotals and a grand total.
• CUBE: Generates all possible subtotals for the grouped columns, including all combinations of the columns and the grand total.

ROLLUP Example:

SELECT department_id, job_id, COUNT(*) 

FROM employees

GROUP BY department_id, job_id

ROLLUP (department_id);

This query groups employees by both department_id and job_id, generating a subtotal for each department_id and a grand total.

CUBE Example:

SELECT department_id, job_id, COUNT(*) 

FROM employees

GROUP BY department_id, job_id

CUBE (department_id, job_id);

This query generates all possible combinations of department_id and job_id, providing subtotals for each combination and a grand total.

8. GROUP BY with DISTINCT

You can use the DISTINCT keyword with GROUP BY to ensure that only unique values are grouped. This is useful when you want to eliminate duplicates in a result set before performing an aggregation.

Example:

SELECT department_id, COUNT(DISTINCT job_id) AS num_unique_jobs

FROM employees

GROUP BY department_id;

This query counts the number of unique job_id values for each department.

9. GROUP BY with Window Functions

Window functions, also known as analytic functions, can be combined with GROUP BY to perform operations across groups while retaining individual row data.

Example:

SELECT department_id, salary, 

       AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary

FROM employees

GROUP BY department_id, salary;

This query calculates the average salary per department, but the result also includes individual salaries for each employee.

10. ORDER BY with GROUP BY

You can use the ORDER BY clause to sort the results of a GROUP BY query. Typically, the sorting is done based on the aggregate values, but you can also sort by non-aggregated columns.

Example:

SELECT department_id, COUNT(*) AS num_employees

FROM employees

GROUP BY department_id

ORDER BY num_employees DESC;

This query counts the number of employees per department and orders the departments in descending order of employee count.

11. GROUP BY with Subqueries

You can use GROUP BY in a subquery to aggregate data before performing further operations on the result set.

Example:

SELECT department_id, AVG(salary)

FROM (SELECT * FROM employees WHERE hire_date > '01-JAN-2020')

GROUP BY department_id;

This query calculates the average salary for each department for employees hired after January 1, 2020, using a subquery to filter the data first.

12. Common Errors with GROUP BY

• Missing non-aggregated columns: If you include a column in the SELECT list that is not part of an aggregate function, and it’s not part of the GROUP BY clause, Oracle will raise an error.
• For example, this query will fail:

SELECT department_id, salary, AVG(salary)

FROM employees

GROUP BY department_id;

To fix the error, you should either:

• Remove salary from the SELECT list, or
• Include salary in the GROUP BY clause (if applicable).

13. GROUP BY with NULL Values

In GROUP BY, NULL values are treated as a single group. If there are rows with NULL values in the grouped columns, those rows will be grouped together.

Example:

SELECT department_id, COUNT(*) 

FROM employees

GROUP BY department_id;

If there are any employees with NULL in the department_id, they will be grouped together under a NULL value.

 

Best Practices for Using GROUP BY

1.     Avoid Grouping by Too Many Columns: Grouping by too many columns can reduce the efficiency of your query. Focus on the necessary columns that provide meaningful results.

2.     Use Aggregate Functions Wisely: Use aggregate functions like SUM(), COUNT(), AVG(), MIN(), and MAX() only when needed to summarize or analyze the data.

3.     Optimize for Large Datasets: When working with large datasets, make sure to create appropriate indexes on the columns used in the GROUP BY clause to improve query performance.

4.     Leverage HAVING for Filtering: Use the HAVING clause to filter groups after aggregation, and avoid using it as a substitute for the WHERE clause.

Conclusion

The GROUP BY clause is a fundamental feature in Oracle SQL for performing aggregations and analyzing data based on groups. It works in combination with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() to provide useful insights into your data. Understanding its syntax, use cases, and best practices is essential for writing efficient queries and optimizing data retrieval.