GROUP BY Clause in Oracle SQL

Key Concepts

1. Grouping Data

The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, like "find the number of customers in each city."

Example:

Grouping customers by city:


        SELECT City, COUNT(CustomerID) AS NumberOfCustomers
        FROM Customers
        GROUP BY City;

2. Aggregating Functions

The GROUP BY clause is often used with aggregate functions like COUNT, SUM, AVG, MAX, and MIN to perform calculations on each group.

Example:

Finding the average salary by department:


        SELECT DepartmentID, AVG(Salary) AS AverageSalary
        FROM Employees
        GROUP BY DepartmentID;

3. Multiple Columns

You can group by multiple columns to create more granular groups. For example, grouping by both city and state.

Example:

Grouping customers by city and state:


        SELECT City, State, COUNT(CustomerID) AS NumberOfCustomers
        FROM Customers
        GROUP BY City, State;

4. HAVING Clause

The HAVING clause is used to filter groups based on a condition. It is often used with aggregate functions.

Example:

Finding departments with more than 10 employees:


        SELECT DepartmentID, COUNT(EmployeeID) AS NumberOfEmployees
        FROM Employees
        GROUP BY DepartmentID
        HAVING COUNT(EmployeeID) > 10;

5. ORDER BY Clause

The ORDER BY clause can be used to sort the grouped results. It is typically placed after the GROUP BY and HAVING clauses.

Example:

Sorting departments by the number of employees in descending order:


        SELECT DepartmentID, COUNT(EmployeeID) AS NumberOfEmployees
        FROM Employees
        GROUP BY DepartmentID
        ORDER BY NumberOfEmployees DESC;

6. NULL Values

The GROUP BY clause treats NULL values as a single group. This can be useful for identifying missing or undefined data.

Example:

Grouping customers by city, including those with no city specified:


        SELECT City, COUNT(CustomerID) AS NumberOfCustomers
        FROM Customers
        GROUP BY City;

7. Subqueries with GROUP BY

The GROUP BY clause can be used in subqueries to perform complex queries that involve grouping and filtering.

Example:

Finding the top 5 departments with the highest average salary:


        SELECT DepartmentID, AverageSalary
        FROM (SELECT DepartmentID, AVG(Salary) AS AverageSalary
              FROM Employees
              GROUP BY DepartmentID)
        ORDER BY AverageSalary DESC
        FETCH FIRST 5 ROWS ONLY;

8. Combining GROUP BY with JOIN

The GROUP BY clause can be combined with JOIN operations to group data from multiple tables.

Example:

Grouping sales by product and calculating the total sales amount:


        SELECT p.ProductName, SUM(s.SalesAmount) AS TotalSales
        FROM Sales s
        JOIN Products p ON s.ProductID = p.ProductID
        GROUP BY p.ProductName;

9. ROLLUP and CUBE

The ROLLUP and CUBE extensions of the GROUP BY clause provide additional grouping options for generating subtotals and grand totals.

Example:

Using ROLLUP to generate subtotals by department and city:


        SELECT DepartmentID, City, COUNT(EmployeeID) AS NumberOfEmployees
        FROM Employees
        GROUP BY ROLLUP(DepartmentID, City);

10. Performance Considerations

Using the GROUP BY clause can impact query performance, especially with large datasets. It is important to optimize queries and consider indexing strategies to ensure efficient execution.

Example:

Optimizing a query by creating an index on the grouping column:


        CREATE INDEX idx_departmentid ON Employees(DepartmentID);