Group Functions (Aggregate Functions) in Oracle 19c SQL

Group functions, also known as aggregate functions, operate on a set of rows and return a single summary row. They are crucial for data analysis and reporting in Oracle databases.

COUNT (All rows, specific columns, DISTINCT count)

The COUNT function is used to count the number of rows in a table or the number of non-null values in a specific column.

Example 1: Counting all rows in a table

SELECT COUNT(*)
FROM employees; -- Counts all rows in the 'employees' table, including those with NULL values

Explanation This Oracle SQL COUNT(*) example retrieves the total number of records from the employees table. It's a fundamental operation for understanding the size of your dataset in Oracle 19c.

Example 2: Counting non-null values in a specific column

SELECT COUNT(commission_pct)
FROM employees; -- Counts rows where 'commission_pct' is not NULL

Explanation This COUNT(column_name) query demonstrates how to count only the rows where the commission_pct column has a non-null value. This is useful for identifying completeness of data within your Oracle database.

Example 3: Counting distinct values in a column

SELECT COUNT(DISTINCT job_id)
FROM employees; -- Counts the number of unique 'job_id' values

Explanation The COUNT(DISTINCT column_name) function in this example returns the number of unique job roles present in the employees table. This is invaluable for understanding the variety of data entries in Oracle 19c.

Example 4: Counting rows with a WHERE clause

SELECT COUNT(*)
FROM employees
WHERE salary > 10000; -- Counts employees with a salary greater than 10000

Explanation This Oracle 19c SQL query uses COUNT(*) with a WHERE clause to count only employees whose salary exceeds 10,000. It's a common pattern for conditional counting in Oracle SQL.

Example 5: Combining COUNT with an alias

SELECT COUNT(*) AS total_employees
FROM employees; -- Assigns an alias 'total_employees' to the count result

Explanation This example showcases how to use an alias (AS total_employees) with the COUNT(*) function. Aliases make your Oracle SQL query results more readable and descriptive.

SUM, AVG, MAX, MIN

These are common aggregate functions used to calculate the sum, average, maximum, and minimum values of numeric columns.

SUM

The SUM function calculates the total sum of a numeric column.

Example 1: Calculating the total salary

SELECT SUM(salary)
FROM employees; -- Calculates the sum of all salaries in the 'employees' table

Explanation This Oracle SQL SUM() example computes the total sum of all salaries in the employees table. It's a core aggregate function for financial analysis in Oracle 19c.

Example 2: Calculating sum for a specific department

SELECT SUM(salary)
FROM employees
WHERE department_id = 90; -- Sums salaries only for department ID 90

Explanation This query demonstrates how to use SUM() with a WHERE clause to calculate the total salary for employees belonging to department ID 90. This is useful for departmental financial reporting in Oracle.

AVG

The AVG function calculates the average value of a numeric column.

Example 1: Calculating the average salary

SELECT AVG(salary)
FROM employees; -- Calculates the average salary of all employees

Explanation This Oracle SQL AVG() example computes the average salary across all employees, providing a quick statistical insight into compensation in Oracle 19c.

Example 2: Calculating average commission percentage

SELECT AVG(commission_pct)
FROM employees; -- Calculates the average commission percentage (ignoring NULLs)

Explanation This AVG(column_name) query determines the average commission_pct among employees, automatically excluding any null values in the calculation, which is standard Oracle SQL behavior for aggregate functions.

MAX

The MAX function finds the highest value in a column.

Example 1: Finding the maximum salary

SELECT MAX(salary)
FROM employees; -- Finds the highest salary among all employees

Explanation This Oracle SQL MAX() example retrieves the highest salary recorded in the employees table, useful for identifying top earners in your Oracle 19c database.

Example 2: Finding the latest hire date

SELECT MAX(hire_date)
FROM employees; -- Finds the latest hire date among all employees

Explanation This MAX(column_name) query can also be used with date columns to find the most recent date, such as the latest hire date, providing insights into employee onboarding trends in Oracle.

MIN

The MIN function finds the lowest value in a column.

Example 1: Finding the minimum salary

SELECT MIN(salary)
FROM employees; -- Finds the lowest salary among all employees

Explanation This Oracle SQL MIN() example identifies the lowest salary paid to an employee, which can be useful for salary range analysis in Oracle 19c.

Example 2: Finding the earliest hire date

SELECT MIN(hire_date)
FROM employees; -- Finds the earliest hire date among all employees

Explanation Similar to MAX(), MIN(column_name) can find the earliest date in a column, like the earliest hire date, providing historical context for your Oracle employee data.

GROUP BY Clause: Grouping Data for Aggregations

The GROUP BY clause is used with aggregate functions to group rows that have the same values in specified columns into a set of summary rows.

Example 1: Total salary by department

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id; -- Groups employees by department and sums their salaries

Explanation This Oracle 19c SQL query uses GROUP BY department_id to calculate the total salary for each distinct department. This is a fundamental way to summarize data by categories in Oracle.

Example 2: Average salary by job ID

SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id; -- Groups employees by job title and calculates average salary

Explanation Here, GROUP BY job_id is used to find the average salary for each unique job role. This helps in understanding compensation patterns across different positions in Oracle.

Example 3: Count of employees per department

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id; -- Counts the number of employees in each department

Explanation This Oracle SQL query demonstrates counting the number of employees within each department using GROUP BY department_id and COUNT(*).

Example 4: Maximum salary by department and job ID

SELECT department_id, job_id, MAX(salary)
FROM employees
GROUP BY department_id, job_id; -- Groups by both department and job, finding max salary for each combination

Explanation This example uses multiple columns in the GROUP BY clause (department_id, job_id) to find the maximum salary for each unique combination of department and job. This provides more granular aggregation in Oracle.

Example 5: Minimum hire date by job ID

SELECT job_id, MIN(hire_date)
FROM employees
GROUP BY job_id; -- Finds the earliest hire date for each job role

Explanation This Oracle 19c SQL query uses GROUP BY job_id to find the earliest hire date for each job position, illustrating how GROUP BY works with date functions.

HAVING Clause: Filtering Groups based on Aggregate Conditions

The HAVING clause is used to filter the results of GROUP BY based on conditions involving aggregate functions. It operates on groups, unlike WHERE which filters individual rows.

Example 1: Departments with average salary greater than 8000

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000; -- Filters groups where the average salary is above 8000

Explanation This Oracle SQL HAVING clause example filters the results of the GROUP BY to show only departments where the calculated average salary is greater than 8000. HAVING is essential for filtering aggregated data in Oracle 19c.

Example 2: Jobs with more than 5 employees

SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id
HAVING COUNT(*) > 5; -- Shows job titles that have more than 5 employees

Explanation This query uses HAVING COUNT(*) > 5 to display only those job roles that have more than five employees, demonstrating how to filter groups based on the count of their members in Oracle.

Example 3: Departments where total salary is between 50000 and 100000

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
HAVING SUM(salary) BETWEEN 50000 AND 100000; -- Filters departments with total salary in a specific range

Explanation This Oracle 19c SQL example utilizes HAVING SUM(salary) BETWEEN 50000 AND 100000 to select departments whose total salary falls within a specified range, showcasing range filtering on aggregated values.

Example 4: Departments with maximum salary greater than 15000

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 15000; -- Filters departments where the highest salary is above 15000

Explanation Here, HAVING MAX(salary) > 15000 is used to find departments where at least one employee earns more than 15000, demonstrating filtering groups based on their maximum aggregate value.

Example 5: Combining WHERE and HAVING

SELECT department_id, AVG(salary)
FROM employees
WHERE hire_date > '01-JAN-2000' -- Filters individual rows first
GROUP BY department_id
HAVING AVG(salary) > 7000; -- Then filters the resulting groups

Explanation This complex Oracle SQL query first filters employees hired after January 1, 2000, using WHERE, and then uses HAVING to show only those departments from the filtered set where the average salary is greater than 7000. This illustrates the order of operations for WHERE and HAVING in Oracle 19c.

GROUPING SETS, ROLLUP, CUBE (Advanced Grouping Options)

These are advanced grouping extensions in Oracle SQL that allow you to generate multiple grouping sets in a single query, providing more comprehensive aggregations. These are powerful tools for data warehousing and business intelligence in Oracle 19c.

GROUPING SETS

GROUPING SETS allows you to define multiple independent grouping criteria in a single query.

Example 1: Total salary by department and by job ID independently

SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id), (job_id));
-- Calculates sum of salary for each department and for each job ID separately

Explanation This Oracle 19c SQL query uses GROUPING SETS to calculate the total salary aggregated by department_id and then independently by job_id in a single result set. This is a flexible way to achieve multiple aggregations efficiently in Oracle.

Example 2: Total salary by department, by job ID, and overall total

SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id), (job_id), ());
-- Calculates sum for each department, each job, and the grand total

Explanation This example expands on GROUPING SETS by including an empty set () to get the grand total of salaries, in addition to sums by department and by job. This is great for summary reports in Oracle 19c.

ROLLUP

ROLLUP generates subtotals for each level of the hierarchy defined by the grouping columns, including a grand total.

Example 1: Total salary by department and subtotal, plus grand total

SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id);
-- Generates subtotals for department-job, department, and grand total

Explanation The ROLLUP operator in this Oracle 19c SQL query generates subtotals for department_id and job_id combinations, subtotals for just department_id, and a grand total for all salaries. It's ideal for hierarchical reporting.

Example 2: Count of employees with ROLLUP on multiple columns

SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY ROLLUP(department_id, job_id);
-- Counts employees at department-job level, department level, and grand total

Explanation This Oracle SQL example shows ROLLUP with COUNT(*), providing counts for each department-job combination, each department, and the overall employee count.

CUBE

CUBE generates subtotals for all possible combinations of the grouping columns, including a grand total.

Example 1: Total salary for all possible combinations of department and job

SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE(department_id, job_id);
-- Generates sums for all possible combinations, including individual and grand totals

Explanation The CUBE operator in this Oracle 19c SQL query produces aggregations for all possible combinations of department_id and job_id, including individual totals and a grand total. This is extremely useful for multidimensional analysis in Oracle.

Example 2: Average salary with CUBE on department and job

SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY CUBE(department_id, job_id);
-- Calculates average salary for all possible combinations

Explanation This Oracle SQL example uses CUBE with AVG(salary) to compute the average salary for every possible combination of department and job, offering a comprehensive view of salary averages across various dimensions.