Mastering SQL for Oracle Fusion HCM –Chapter 10. SQL GROUP BY and Aggregations in Fusion HCM

 

✅Chapter 10. SQL GROUP BY and Aggregations in Fusion HCM

SQL GROUP BY and Aggregations in Oracle Fusion HCM – Aggregate Functions

Learn how to use SQL GROUP BY and aggregation functions in Oracle Fusion HCM. Includes examples for SUM, AVG, COUNT, and more with real-world use cases.

---

🔹 Introduction

The GROUP BY clause is an essential part of SQL queries, especially when you need to summarize or aggregate data. In Oracle Fusion HCM, you often need to group data by certain attributes (like department, job, or employee status) and calculate aggregates (such as total salary, average age, or count of employees).

This chapter explains how to use SQL aggregation functions (e.g., SUM, AVG, COUNT, MAX, MIN) along with the GROUP BY clause to generate meaningful reports and insights from Fusion HCM data.

---

🔹 Theoretical Concepts

📌 What is GROUP BY?

The GROUP BY clause groups rows that have the same values in specified columns into aggregated data. It is often used in conjunction with aggregate functions to summarize data.

📌 Aggregate Functions

Function	Description	Example
SUM()	Adds up values in a column	SUM(salary)
AVG()	Calculates the average of values	AVG(age)
COUNT()	Counts the number of rows	COUNT(*)
MAX()	Returns the highest value	MAX(salary)
MIN()	Returns the lowest value	MIN(salary)

📌 Syntax of GROUP BY

SELECT column_name,
       AGGREGATE_FUNCTION(column_name)
FROM   table_name
WHERE  CONDITION
GROUP  BY column_name 

📌 HAVING Clause

• The HAVING clause is used to filter results after aggregation, similar to WHERE but for grouped data.

---

🔹 Common GROUP BY Use Cases in Fusion HCM

✅ Example 1: Total Salary by Department

Scenario: Calculate the total salary for each department.

SELECT hd.department_name,
       SUM(paam.salary) AS total_salary
FROM   per_all_assignments_m paam,
       hr_all_departments hd
WHERE  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND
                              paam.effective_end_date
       AND paam.department_id = hd.department_id
       AND TRUNC(SYSDATE) BETWEEN hd.effective_start_date AND
                                  hd.effective_end_date
GROUP  BY hd.department_name 

✅ Example 2: Average Age of Employees in Each Department

Scenario: Calculate the average age of employees by department.

SELECT hd.department_name,
       AVG(TRUNC(MONTHS_BETWEEN(SYSDATE, papf.date_of_birth) / 12)) AS avg_age
FROM   per_all_people_f papf,
       per_all_assignments_m paam,
       hr_all_departments hd
WHERE  TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND
                              papf.effective_end_date
       AND paam.department_id = hd.department_id
       AND papf.person_id = paam.person_id
       AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND
                                  paam.effective_end_date
       AND TRUNC(SYSDATE) BETWEEN hd.effective_start_date AND
                                  hd.effective_end_date
GROUP  BY hd.department_name 

✅ Example 3: Count of Employees by Job Title

Scenario: Count the number of employees in each job role.

SELECT pj.job_name,
       COUNT(*) AS employee_count
FROM   per_all_assignments_m paam,
       per_jobs pj
WHERE  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND
                              paam.effective_end_date
       AND paam.job_id = pj.job_id
       AND TRUNC(SYSDATE) BETWEEN pj.effective_start_date AND
                                  pj.effective_end_date
GROUP  BY pj.job_name 

✅ Example 4: Maximum and Minimum Salary by Job Title

Scenario: Get the highest and lowest salaries for each job title.

SELECT pj.job_name,
       MAX(paam.salary) AS max_salary,
       MIN(paam.salary) AS min_salary
FROM   per_all_assignments_m paam,
       per_jobs pj
WHERE  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND
                              paam.effective_end_date
       AND paam.job_id = pj.job_id
       AND TRUNC(SYSDATE) BETWEEN pj.effective_start_date AND
                                  pj.effective_end_date
GROUP  BY pj.job_name 

---

🔹 Using the HAVING Clause

The HAVING clause filters the grouped data, allowing you to apply conditions after the aggregation.

✅ Example: Total Salary Greater Than a Threshold

Scenario: Show only departments where the total salary exceeds $1,000,000.

SELECT hd.department_name,
       SUM(paam.salary) AS total_salary
FROM   per_all_assignments_m paam,
       hr_all_departments hd
WHERE  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND
                              paam.effective_end_date
       AND paam.department_id = hd.department_id
       AND TRUNC(SYSDATE) BETWEEN hd.effective_start_date AND
                                  hd.effective_end_date
GROUP  BY hd.department_name
HAVING SUM(paam.salary) > 1000000 

---

🔹 Real-Time Scenario (Fusion HCM BI Publisher Reporting)

🧾 Scenario:

“Create a BI Publisher report displaying the total number of employees by job title, but only for titles where the count exceeds 10 employees.”

✅ SQL Query:

SELECT pj.job_name,
       COUNT(*) AS employee_count
FROM   per_all_assignments_m paam
       ,per_jobs pj
        
WHERE  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND
                              paam.effective_end_date
       AND paam.job_id = pj.job_id
       AND TRUNC(SYSDATE) BETWEEN pj.effective_start_date AND
                                  pj.effective_end_date
GROUP  BY pj.job_name
HAVING COUNT(*) > 10 

---

🔹 Practice Exercise

💡 Task: Write a query to find the average salary by department, showing only departments with an average salary greater than $50,000.

SELECT hd.department_name,
       AVG(paam.salary) AS avg_salary
FROM   per_all_assignments_m paam,
       hr_all_departments hd
WHERE  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND
                              paam.effective_end_date
       AND paam.department_id = hd.department_id
       AND TRUNC(SYSDATE) BETWEEN hd.effective_start_date AND
                                  hd.effective_end_date
GROUP  BY hd.department_name
HAVING AVG(paam.salary) > 50000 

---

🔹 Best Practices

• Use GROUP BY when summarizing data, such as total, average, or count by specific fields (e.g., department, job title).

• Always filter your data with WHERE before grouping to improve performance.

• Combine HAVING with aggregate functions to filter after the aggregation.

• Avoid grouping by columns with high cardinality (e.g., employee IDs) unless necessary.

---

🔹 Summary

✅ SQL GROUP BY is used to aggregate data by specific fields in Fusion HCM, such as department, job, and assignment.
✅ Common aggregate functions include SUM, AVG, COUNT, MAX, and MIN.
✅ The HAVING clause helps filter the grouped results based on aggregated values.
✅ Use GROUP BY in BI Publisher and Fusion HCM reports for better insights.

---

🔹 Next Steps

• Previous Chapter: Mastering SQL for Oracle Fusion HCM –Chapter 9. SQL JOINS – Combining Tables in Fusion HCM

• Next Chapter: Mastering SQL for Oracle Fusion HCM – Chapter 11. SQL Subqueries and Nested Queries in Fusion HCM

Tags: #SQLGroupBy, #FusionHCM, #Aggregations, #BIReports, #DataSummary

---