10 Grouping and Aggregation in Oracle SQL
Key Concepts
Grouping and aggregation in Oracle SQL are essential for summarizing and analyzing data. Understanding the following key concepts is crucial for effectively using these techniques:
1. GROUP BY Clause
The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. This is often used with aggregate functions to perform calculations on each group.
Example: Grouping sales data by region:
SELECT Region, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY Region;
2. Aggregate Functions
Aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include SUM, AVG, COUNT, MAX, and MIN.
Example: Calculating the average salary of employees:
SELECT AVG(Salary) AS AverageSalary
FROM Employees;
3. HAVING Clause
The HAVING clause is used to filter groups based on the result of an aggregate function. It is often used in conjunction with the GROUP BY clause.
Example: Filtering regions with total sales greater than $1,000,000:
SELECT Region, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY Region
HAVING SUM(SalesAmount) > 1000000;
4. ROLLUP
The ROLLUP extension to the GROUP BY clause creates subtotals and a grand total for hierarchical levels of analysis.
Example: Creating subtotals and a grand total for sales by region and product:
SELECT Region, Product, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Region, Product);
5. CUBE
The CUBE extension to the GROUP BY clause creates subtotals for all possible combinations of the specified columns.
Example: Creating subtotals for all combinations of region and product:
SELECT Region, Product, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY CUBE(Region, Product);
6. GROUPING SETS
The GROUPING SETS clause allows you to specify multiple grouping combinations in a single query.
Example: Grouping sales data by region and product, and also by region alone:
SELECT Region, Product, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS((Region, Product), (Region));
7. GROUPING Function
The GROUPING function is used to identify whether a column is included in the current grouping set. It returns 1 if the column is not included, and 0 if it is.
Example: Identifying rows that are subtotals:
SELECT Region, Product, SUM(SalesAmount) AS TotalSales, GROUPING(Region) AS IsRegionGrouped
FROM Sales
GROUP BY ROLLUP(Region, Product);
8. GROUP_ID Function
The GROUP_ID function is used to identify duplicate groups in a query. It returns a unique number for each group.
Example: Identifying duplicate groups in a query:
SELECT Region, Product, SUM(SalesAmount) AS TotalSales, GROUP_ID() AS GroupID
FROM Sales
GROUP BY GROUPING SETS((Region, Product), (Region, Product));
9. Analytical Functions
Analytical functions perform calculations across a set of table rows that are somehow related to the current row. They are similar to aggregate functions but do not group rows into a single output row.
Example: Calculating the running total of sales:
SELECT OrderDate, SalesAmount, SUM(SalesAmount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Sales;
10. PIVOT and UNPIVOT
The PIVOT operator is used to transform rows into columns, and the UNPIVOT operator is used to transform columns into rows.
Example: Pivoting sales data by product:
SELECT *
FROM (SELECT Region, Product, SalesAmount FROM Sales)
PIVOT (SUM(SalesAmount) FOR Product IN ('ProductA' AS ProductA, 'ProductB' AS ProductB));