/*===============================================================
SQL Functions
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 05A β AGGREGATE FUNCTIONS & GROUP BY
Topics Covered
--------------
β’ Aggregate functions
β’ COUNT
β’ SUM
β’ AVG
β’ MIN / MAX
β’ GROUP BY
β’ HAVING clause
β’ Practical HR reporting queries
===============================================================*/
/*---------------------------------------------------------------
AGGREGATE FUNCTIONS INTRODUCTION
---------------------------------------------------------------*/
/*
Aggregate functions operate on groups of rows
and return a single result.
Common aggregate functions:
COUNT()
SUM()
AVG()
MIN()
MAX()
*/
/*---------------------------------------------------------------
QUESTION 1
Total number of employees
---------------------------------------------------------------*/
SELECT
COUNT(*) AS total_employees
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 2
Total salary expense
---------------------------------------------------------------*/
SELECT
SUM(salary) AS total_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 3
Average salary
---------------------------------------------------------------*/
SELECT
AVG(salary) AS avg_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 4
Highest salary
---------------------------------------------------------------*/
SELECT
MAX(salary) AS highest_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 5
Lowest salary
---------------------------------------------------------------*/
SELECT
MIN(salary) AS lowest_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 6
Total commission count
---------------------------------------------------------------*/
SELECT
COUNT(commission_pct)
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 7
Employees with commissions
---------------------------------------------------------------*/
SELECT
COUNT(*)
FROM hr.employees
WHERE commission_pct IS NOT NULL;
/*---------------------------------------------------------------
GROUP BY CLAUSE
---------------------------------------------------------------*/
/*
GROUP BY groups rows that share
the same values.
*/
/*---------------------------------------------------------------
QUESTION 8
Average salary per department
---------------------------------------------------------------*/
SELECT
department_id,
AVG(salary)
FROM hr.employees
GROUP BY department_id;
/*---------------------------------------------------------------
QUESTION 9
Total salary per department
---------------------------------------------------------------*/
SELECT
department_id,
SUM(salary)
FROM hr.employees
GROUP BY department_id;
/*---------------------------------------------------------------
QUESTION 10
Employee count per department
---------------------------------------------------------------*/
SELECT
department_id,
COUNT(*)
FROM hr.employees
GROUP BY department_id;
/*---------------------------------------------------------------
QUESTION 11
Maximum salary per department
---------------------------------------------------------------*/
SELECT
department_id,
MAX(salary)
FROM hr.employees
GROUP BY department_id;
/*---------------------------------------------------------------
QUESTION 12
Minimum salary per department
---------------------------------------------------------------*/
SELECT
department_id,
MIN(salary)
FROM hr.employees
GROUP BY department_id;
/*---------------------------------------------------------------
QUESTION 13
Average salary per job
---------------------------------------------------------------*/
SELECT
job_id,
AVG(salary)
FROM hr.employees
GROUP BY job_id;
/*---------------------------------------------------------------
QUESTION 14
Employee count per job
---------------------------------------------------------------*/
SELECT
job_id,
COUNT(*)
FROM hr.employees
GROUP BY job_id;
/*---------------------------------------------------------------
MULTIPLE COLUMN GROUP BY
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 15
Department and job salary totals
---------------------------------------------------------------*/
SELECT
department_id,
job_id,
SUM(salary)
FROM hr.employees
GROUP BY department_id, job_id;
/*---------------------------------------------------------------
QUESTION 16
Department and job employee count
---------------------------------------------------------------*/
SELECT
department_id,
job_id,
COUNT(*)
FROM hr.employees
GROUP BY department_id, job_id;
/*---------------------------------------------------------------
HAVING CLAUSE
---------------------------------------------------------------*/
/*
HAVING filters grouped results.
*/
/*---------------------------------------------------------------
QUESTION 17
Departments with average salary > 10000
---------------------------------------------------------------*/
SELECT
department_id,
AVG(salary)
FROM hr.employees
GROUP BY department_id
HAVING AVG(salary) > 10000;
/*---------------------------------------------------------------
QUESTION 18
Departments with more than 5 employees
---------------------------------------------------------------*/
SELECT
department_id,
COUNT(*)
FROM hr.employees
GROUP BY department_id
HAVING COUNT(*) > 5;
/*---------------------------------------------------------------
QUESTION 19
Jobs with average salary above 9000
---------------------------------------------------------------*/
SELECT
job_id,
AVG(salary)
FROM hr.employees
GROUP BY job_id
HAVING AVG(salary) > 9000;
/*---------------------------------------------------------------
QUESTION 20
Departments with total salary above 50000
---------------------------------------------------------------*/
SELECT
department_id,
SUM(salary)
FROM hr.employees
GROUP BY department_id
HAVING SUM(salary) > 50000;
/*---------------------------------------------------------------
AGGREGATE REPORTING
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 21
Department salary report
---------------------------------------------------------------*/
SELECT
department_id,
COUNT(*) employee_count,
SUM(salary) total_salary,
AVG(salary) avg_salary,
MAX(salary) max_salary,
MIN(salary) min_salary
FROM hr.employees
GROUP BY department_id;
/*---------------------------------------------------------------
QUESTION 22
Job salary report
---------------------------------------------------------------*/
SELECT
job_id,
COUNT(*) employee_count,
SUM(salary) total_salary,
AVG(salary) avg_salary
FROM hr.employees
GROUP BY job_id;
/*---------------------------------------------------------------
QUESTION 23
Department salary sorted
---------------------------------------------------------------*/
SELECT
department_id,
AVG(salary)
FROM hr.employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;
/*---------------------------------------------------------------
QUESTION 24
Department employee counts sorted
---------------------------------------------------------------*/
SELECT
department_id,
COUNT(*)
FROM hr.employees
GROUP BY department_id
ORDER BY COUNT(*) DESC;
/*---------------------------------------------------------------
QUESTION 25
Highest paying department
---------------------------------------------------------------*/
SELECT
department_id,
AVG(salary)
FROM hr.employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
FETCH FIRST 1 ROW ONLY;
/*---------------------------------------------------------------
PERFORMANCE NOTES
---------------------------------------------------------------*/
/*
Aggregation Best Practices
1 Use GROUP BY efficiently
2 Avoid unnecessary grouping
3 Use HAVING only after grouping
4 Filter rows before grouping
*/
/*---------------------------------------------------------------
END OF PART 05A
---------------------------------------------------------------*/
/*===============================================================
PART 05B β ADVANCED AGGREGATION
Continuation of PART 05A
Topics Covered
--------------
β’ LISTAGG
β’ ROLLUP
β’ CUBE
β’ GROUPING SETS
β’ GROUPING / GROUPING_ID
β’ MEDIAN
β’ PERCENTILE
β’ APPROX_COUNT_DISTINCT
===============================================================*/
/*---------------------------------------------------------------
LISTAGG FUNCTION
---------------------------------------------------------------*/
/*
LISTAGG concatenates multiple rows into one string.
*/
/*---------------------------------------------------------------
QUESTION 26
List employee names per department
---------------------------------------------------------------*/
SELECT
department_id,
LISTAGG(last_name, ', ')
WITHIN GROUP (ORDER BY last_name) AS employee_list
FROM hr.employees
GROUP BY department_id;
/*---------------------------------------------------------------
QUESTION 27
List employees per job
---------------------------------------------------------------*/
SELECT
job_id,
LISTAGG(first_name, ', ')
WITHIN GROUP (ORDER BY first_name) employee_names
FROM hr.employees
GROUP BY job_id;
/*---------------------------------------------------------------
QUESTION 28
List employees per department ordered by salary
---------------------------------------------------------------*/
SELECT
department_id,
LISTAGG(first_name, ', ')
WITHIN GROUP (ORDER BY salary DESC) employee_names
FROM hr.employees
GROUP BY department_id;
/*---------------------------------------------------------------
ROLLUP
---------------------------------------------------------------*/
/*
ROLLUP generates hierarchical totals.
*/
/*---------------------------------------------------------------
QUESTION 29
Salary totals by department and job
---------------------------------------------------------------*/
SELECT
department_id,
job_id,
SUM(salary) total_salary
FROM hr.employees
GROUP BY ROLLUP(department_id, job_id);
/*---------------------------------------------------------------
QUESTION 30
Department salary totals including grand total
---------------------------------------------------------------*/
SELECT
department_id,
SUM(salary)
FROM hr.employees
GROUP BY ROLLUP(department_id);
/*---------------------------------------------------------------
QUESTION 31
Job salary totals with grand total
---------------------------------------------------------------*/
SELECT
job_id,
SUM(salary)
FROM hr.employees
GROUP BY ROLLUP(job_id);
/*---------------------------------------------------------------
CUBE
---------------------------------------------------------------*/
/*
CUBE generates all possible subtotal combinations.
*/
/*---------------------------------------------------------------
QUESTION 32
Department and job salary cube
---------------------------------------------------------------*/
SELECT
department_id,
job_id,
SUM(salary) total_salary
FROM hr.employees
GROUP BY CUBE(department_id, job_id);
/*---------------------------------------------------------------
QUESTION 33
Department job employee cube
---------------------------------------------------------------*/
SELECT
department_id,
job_id,
COUNT(*) employee_count
FROM hr.employees
GROUP BY CUBE(department_id, job_id);
/*---------------------------------------------------------------
GROUPING FUNCTION
---------------------------------------------------------------*/
/*
GROUPING identifies aggregated rows.
*/
/*---------------------------------------------------------------
QUESTION 34
Identify subtotal rows
---------------------------------------------------------------*/
SELECT
department_id,
job_id,
SUM(salary) total_salary,
GROUPING(department_id) dept_group,
GROUPING(job_id) job_group
FROM hr.employees
GROUP BY CUBE(department_id, job_id);
/*---------------------------------------------------------------
GROUPING_ID
---------------------------------------------------------------*/
/*
GROUPING_ID identifies grouping level.
*/
/*---------------------------------------------------------------
QUESTION 35
Grouping level identification
---------------------------------------------------------------*/
SELECT
department_id,
job_id,
SUM(salary),
GROUPING_ID(department_id, job_id) grouping_level
FROM hr.employees
GROUP BY CUBE(department_id, job_id);
/*---------------------------------------------------------------
GROUPING SETS
---------------------------------------------------------------*/
/*
GROUPING SETS allow custom aggregation levels.
*/
/*---------------------------------------------------------------
QUESTION 36
Custom grouping report
---------------------------------------------------------------*/
SELECT
department_id,
job_id,
SUM(salary)
FROM hr.employees
GROUP BY GROUPING SETS
(
(department_id, job_id),
(department_id),
(job_id),
()
);
/*---------------------------------------------------------------
QUESTION 37
Department and job grouping
---------------------------------------------------------------*/
SELECT
department_id,
job_id,
COUNT(*)
FROM hr.employees
GROUP BY GROUPING SETS
(
(department_id, job_id),
(department_id)
);
/*---------------------------------------------------------------
STATISTICAL AGGREGATE FUNCTIONS
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 38
Median salary
---------------------------------------------------------------*/
SELECT
MEDIAN(salary)
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 39
90th percentile salary
---------------------------------------------------------------*/
SELECT
PERCENTILE_CONT(0.9)
WITHIN GROUP (ORDER BY salary) salary_90_percentile
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 40
Discrete median salary
---------------------------------------------------------------*/
SELECT
PERCENTILE_DISC(0.5)
WITHIN GROUP (ORDER BY salary)
FROM hr.employees;
/*---------------------------------------------------------------
APPROXIMATE AGGREGATION
---------------------------------------------------------------*/
/*
Oracle provides approximate functions
for large datasets.
*/
/*---------------------------------------------------------------
QUESTION 41
Approximate count of distinct departments
---------------------------------------------------------------*/
SELECT
APPROX_COUNT_DISTINCT(department_id)
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 42
Exact distinct department count
---------------------------------------------------------------*/
SELECT
COUNT(DISTINCT department_id)
FROM hr.employees;
/*---------------------------------------------------------------
ADVANCED REPORT
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 43
Department salary statistics
---------------------------------------------------------------*/
SELECT
department_id,
COUNT(*) employee_count,
SUM(salary) total_salary,
AVG(salary) avg_salary,
MEDIAN(salary) median_salary,
MAX(salary) max_salary,
MIN(salary) min_salary
FROM hr.employees
GROUP BY department_id;
/*---------------------------------------------------------------
QUESTION 44
Job salary statistics
---------------------------------------------------------------*/
SELECT
job_id,
COUNT(*) employee_count,
AVG(salary) avg_salary,
MEDIAN(salary) median_salary
FROM hr.employees
GROUP BY job_id;
/*---------------------------------------------------------------
QUESTION 45
Top 3 highest paying departments
---------------------------------------------------------------*/
SELECT *
FROM
(
SELECT
department_id,
AVG(salary) avg_salary
FROM hr.employees
GROUP BY department_id
ORDER BY avg_salary DESC
)
WHERE ROWNUM <= 3;
/*---------------------------------------------------------------
QUESTION 46
Department salary ranking
---------------------------------------------------------------*/
SELECT
department_id,
AVG(salary),
RANK() OVER (ORDER BY AVG(salary) DESC) dept_rank
FROM hr.employees
GROUP BY department_id;
/*---------------------------------------------------------------
PERFORMANCE NOTES
---------------------------------------------------------------*/
/*
Aggregation Optimization
1 Use indexes on grouping columns
2 Filter rows before aggregation
3 Avoid unnecessary grouping columns
4 Use approximate aggregation for large datasets
*/
/*---------------------------------------------------------------
END OF PART 05
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express
or implied warranties, including but not limited to warranties of accuracy, completeness,
reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges
and agrees that they do so at their own risk. The author shall not be held liable for any
direct, indirect, incidental, consequential, special, or exemplary damages, including but
not limited to loss of data, loss of profits, system failures, security breaches, or any
other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries,
and configurations in a controlled, non-production (test) environment prior to deploying
them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials
complies with all applicable local, national, and international laws, regulations,
and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless
the author from any claims, damages, liabilities, or expenses arising from their use.
---------------------------------------------------------------*/