Oracle: Aggregate Functions for Databases

Aggregate functions are essential for summarizing and analyzing large data sets. This article will guide you from basic to advanced concepts, exploring how to use functions such as SUM, AVG, MIN, MAX, and COUNT, together with the GROUP BY clause, to get the most out of your data.

Introduction

Imagine that you are working with a database full of information and need to calculate averages and totals or find minimum and maximum values. Oracle aggregate functions let you perform these tasks quickly and effectively. In this article, we will see how to use them with practical examples and useful tips.

Basic Aggregate Functions

SUM: Adding Values

The SUM function calculates the total of a set of numeric values.

Syntax:

SELECT SUM(columna) AS total FROM tabla;

Example:

SELECT SUM(salario) AS total_salarios FROM empleados;

Result: Total salaries in the company

AVG: Averaging Values

The AVG function calculates the average of a set of numeric values.

Syntax:

SELECT AVG(columna) AS promedio FROM tabla;

Example:

SELECT AVG(salario) AS salario_promedio FROM empleados;

Result: Average employee salary

MIN and MAX: Minimum and Maximum Values

The MIN and MAX functions return the minimum and maximum value of a set of values, respectively.

Syntax:

SELECT MIN(columna) AS valor_minimo, MAX(columna) AS valor_maximo FROM tabla;

Example:

SELECT MIN(salario) AS salario_minimo, MAX(salario) AS salario_maximo FROM empleados;

Result: Minimum and maximum salary in the company

COUNT: Counting Rows

The COUNT function counts the number of rows that meet a specific condition.

Syntax:

SELECT COUNT(columna) AS total FROM tabla;

Example:

SELECT COUNT(*) AS total_empleados FROM empleados;

Result: Total number of employees

Using the GROUP BY Clause

The GROUP BY clause groups rows that have the same values in specific columns and allows aggregate functions to be applied to each group.

Syntax:

SELECT column, aggregate_function(column) FROM table_name GROUP BY column;

Example:

SELECT departamento, SUM(salario) AS total_salarios
FROM empleados
GROUP BY departamento;

Result: Total salaries by department

Complete Example

Suppose we want to obtain the total, average, minimum, and maximum salary for each department in our employee table.

Query:

SELECT departamento, 
       SUM(salario) AS total_salarios, 
       AVG(salario) AS salario_promedio, 
       MIN(salario) AS salario_minimo, 
       MAX(salario) AS salario_maximo
FROM empleados
GROUP BY departamento;

Result: Detailed salary summary by department

Tips and Best Practices

Filtering Data with HAVING

Sometimes, after grouping data, you need to filter the results. The HAVING clause is used for this purpose.

Example:

SELECT departamento, SUM(salario) AS total_salarios
FROM empleados
GROUP BY departamento
HAVING SUM(salario) > 100000;

Result: Departments with total salaries above 100,000

Combining with Other Functions

You can combine aggregate functions with other functions to obtain more complex results.

Example:

SELECT departamento, 
       ROUND(AVG(salario), 2) AS salario_promedio_redondeado
FROM empleados
GROUP BY departamento;

Result: Average salary by department, rounded to two decimal places

Conclusion

Oracle aggregate functions are powerful tools for summarizing and analyzing data. From calculating totals and averages to finding minimum and maximum values, these functions let you obtain valuable information from your data. With the GROUP BY clause, you can group data and apply these functions to each group to create detailed summaries.

Now that you know the basic and advanced aggregate functions in Oracle, together with some practical tips, you are ready to apply them to your projects and optimize data analysis in your database.

Ready to transform your data with Oracle? Start practicing today and discover everything you can achieve with these aggregate functions.