Oracle SQL Basic Lesson 9
Overview
Oracle SQL is a powerful language for interacting with relational databases, and group functions are one of its most useful features. Group functions operate on sets of rows to return a single result per group. These functions are essential in reporting, analysis, and summarizing data. In this blog, we’ll explore the most commonly used Oracle SQL group functions with explanations and practical examples.
Summary Table
- AVG | Returns average of values
- COUNT | Counts rows
- MAX | Returns highest value
- MIN | Returns lowest value
- SUM | Returns total sum of values
- STDDEV | Measures standard deviation
- VARIANCE | Measures variance of data
- MEDIAN | Returns the middle value
- LISTAGG | Aggregates strings into one row
- GROUPING | Flags subtotal or grand total rows
1. AVG (Average)
The AVG() function returns the average of a numeric column. It ignores NULL values.
Example 1:
SELECT AVG(salary) AS avg_salary
FROM employees;
Example 2:
SELECT department_id, AVG(salary) AS avg_dept_salary
FROM employees
GROUP BY department_id;
Explanation:
The AVG function is used to calculate the mean (average) value. It’s often used in combination with GROUP BY to find averages per group (e.g., per department). Useful in financial and performance reports.
2. COUNT (Count Rows)
The COUNT() function counts the number of rows that match a specified condition.
Example 1:
SELECT COUNT(*) AS total_employees
FROM employees;
Example 2:
SELECT department_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id;
Explanation:
COUNT(*) counts all rows, including those with NULLs. COUNT(column_name) or COUNT(1) only counts non-null values.
It's widely used in summarizing records, validations, and generating row-based statistics.
3. MAX (Maximum Value)
The MAX() function returns the highest value in a column.
Example 1:
SELECT MAX(salary) AS highest_salary
FROM employees;
Example 2:
SELECT department_id, MAX(salary) AS max_salary_per_dept
FROM employees
GROUP BY department_id;
Explanation:
The MAX function is useful for identifying the top values in a dataset, such as the highest salary or the most recent date. It works with both numbers and dates.
4. MIN (Minimum Value)
The MIN() function returns the lowest value in a column.
Example 1:
SELECT MIN(salary) AS lowest_salary
FROM employees;
Example 2:
SELECT department_id, MIN(hire_date) AS earliest_hire
FROM employees
GROUP BY department_id;
Explanation:
Similar to MAX, the MIN function finds the smallest value in a column. It’s often used to find the earliest dates or lowest figures within grouped data.
5. SUM (Total Value)
The SUM() function returns the total sum of a numeric column.
Example 1:
SELECT SUM(salary) AS total_salary
FROM employees;
Example 2:
SELECT department_id, SUM(salary) AS dept_salary_total
FROM employees
GROUP BY department_id;
Explanation:
SUM is used to calculate the total value across a column. It’s essential in financial and sales data where cumulative figures are required.
6. STDDEV (Standard Deviation)
The STDDEV() function returns the standard deviation of numeric values.
Example 1:
SELECT STDDEV(salary) AS salary_stddev
FROM employees;
Example 2:
SELECT department_id, STDDEV(salary) AS dept_salary_stddev
FROM employees
GROUP BY department_id;
Explanation:
STDDEV measures the dispersion of values from the average. It helps identify how spread out data is and is useful in statistical analysis.
7. VARIANCE
The VARIANCE() function returns the statistical variance of numeric data.
Example 1:
SELECT VARIANCE(salary) AS salary_variance
FROM employees;
Example 2:
SELECT department_id, VARIANCE(salary) AS dept_salary_variance
FROM employees
GROUP BY department_id;
Explanation:
Variance shows the degree of spread in the data. It’s often used alongside STDDEV to get a better statistical understanding of numeric columns.
8. MEDIAN (Median Value)
The MEDIAN() function returns the middle value of a numeric column.
Example 1:
SELECT MEDIAN(salary) AS median_salary
FROM employees;
Example 2:
SELECT department_id, MEDIAN(salary) AS median_salary_per_dept
FROM employees
GROUP BY department_id;
Explanation:
MEDIAN is useful when analyzing skewed distributions, as it provides a central value that isn’t distorted by outliers. It’s helpful in salary and budget analysis.
9. LISTAGG (String Aggregation)
The LISTAGG() function combines values from multiple rows into a single string.
Example 1:
SELECT LISTAGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name) AS all_names
FROM employees;
Example 2:
SELECT department_id,
LISTAGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name) AS dept_names
FROM employees
GROUP BY department_id;
Explanation:
LISTAGG is a powerful way to turn rows into comma-separated strings. It’s useful for reporting, dashboards, and user-facing data presentation.
10. GROUPING (Differentiating Grouped Rows)
The GROUPING() function is used with GROUP BY CUBE or ROLLUP to identify subtotal and grand total rows.
Example 1:
SELECT department_id, job_id, COUNT(*) AS emp_count,
GROUPING(department_id) AS dept_grouped,
GROUPING(job_id) AS job_grouped
FROM employees
GROUP BY ROLLUP(department_id, job_id);
Example 2:
SELECT department_id, COUNT(*) AS emp_count,
GROUPING(department_id) AS is_total
FROM employees
GROUP BY ROLLUP(department_id);
Explanation:
GROUPING() helps distinguish subtotal and total rows generated by ROLLUP or CUBE. It returns 1 for aggregated rows and 0 for detailed ones.
Conclusion
Oracle SQL group functions are essential tools for anyone working with data. Whether you're summarizing sales, analyzing trends, or preparing reports, mastering these functions can make your SQL queries more powerful and insightful. Start with the basics like AVG, COUNT, and SUM, then move on to more advanced ones like STDDEV, LISTAGG, and GROUPING.
0 Comments