Agregación
## Objetivos de Aprendizaje
- Dominar funciones de agregación
- Usar GROUP BY efectivamente
- Filtrar grupos con HAVING
- Entender COUNT, SUM, AVG, MIN, MAX
## Funciones de Agregación
### Funciones Comunes
| Función | Descripción |
|---------|-------------|
| COUNT() | Contar filas |
| SUM() | Sumar valores |
| AVG() | Promedio de valores |
| MIN() | Valor mínimo |
| MAX() | Valor máximo |
## COUNT
### Contar Todas las Filas
```sql
SELECT COUNT(*)
FROM employees;
```
### Contar Valores No-NULL
```sql
SELECT COUNT(email)
FROM employees;
```
### Contar Distintos
```sql
SELECT COUNT(DISTINCT department)
FROM employees;
```
## SUM
```sql
SELECT SUM(salary)
FROM employees;
```
### Con WHERE
```sql
SELECT SUM(salary)
FROM employees
WHERE department = 'Sales';
```
### Múltiples Columnas
```sql
SELECT
SUM(salary) AS total_salary,
SUM(bonus) AS total_bonus
FROM employees;
```
## AVG
```sql
SELECT AVG(salary)
FROM employees;
```
### AVG con WHERE
```sql
SELECT AVG(salary)
FROM employees
WHERE department = 'Engineering';
```
### Redondear Resultados
```sql
SELECT ROUND(AVG(salary), 2)
FROM employees;
```
## MIN y MAX
```sql
SELECT
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees;
```
### Ejemplos con Fechas
```sql
-- Contratación más temprana
SELECT MIN(hire_date)
FROM employees;
-- Contratación más reciente
SELECT MAX(hire_date)
FROM employees;
```
## GROUP BY
### Agrupamiento Básico
```sql
SELECT department, COUNT(*) as count
FROM employees
GROUP BY department;
```
### Resultado de GROUP BY
```text
department | count
-------------+------
Engineering | 15
Sales | 12
Marketing | 8
```
### GROUP BY Múltiples Columnas
```sql
SELECT
department,
city,
COUNT(*) as headcount,
AVG(salary) as avg_salary
FROM employees
GROUP BY department, city;
```
### Agregación Sin GROUP BY
```sql
-- Toda agregación, sin agrupamiento
SELECT
COUNT(*) as total_employees,
SUM(salary) as total_payroll
FROM employees;
```
## HAVING
### Filtrar Grupos
```sql
SELECT
department,
COUNT(*) as headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
```
### WHERE vs HAVING
| Cláusula | Filtra | Aplicado |
|----------|--------|----------|
| WHERE | Filas | Antes de GROUP BY |
| HAVING | Grupos | Después de GROUP BY |
### Ejemplo Combinado
```sql
SELECT
department,
COUNT(*) as headcount,
AVG(salary) as avg_salary
FROM employees
WHERE salary > 40000
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY avg_salary DESC;
```
### Orden de Ejecución
1. WHERE filtra filas
2. GROUP BY agrupa filas filtradas
3. HAVING filtra grupos
4. ORDER BY ordena resultado
## COUNT con NULL
```sql
-- COUNT(*) cuenta todas las filas incluyendo NULL
SELECT COUNT(*) FROM employees; -- Todas las filas
-- COUNT(columna) cuenta solo valores no-NULL
SELECT COUNT(manager_id) FROM employees; -- Solo no-NULL
```
## AVG y NULL
```sql
-- AVG ignora valores NULL
-- AVG(1, 2, NULL, 4) = AVG(1, 2, 4) = 2.33
```
## GROUP BY con JOIN
```sql
SELECT
d.department_name,
COUNT(e.employee_id) as headcount,
AVG(e.salary) as avg_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.department_name
ORDER BY avg_salary DESC;
```
## COUNT con Case
```sql
SELECT
department,
COUNT(*) AS total,
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active,
SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive
FROM employees
GROUP BY department;
```
## DISTINCT en Agregados
```sql
-- Contar departamentos únicos
SELECT COUNT(DISTINCT department)
FROM employees;
-- Promedio de salarios distintos (si existen duplicados)
SELECT AVG(DISTINCT salary)
FROM employees;
```
## Múltiples Agregados
```sql
SELECT
department,
COUNT(*) as headcount,
SUM(salary) as total_salary,
ROUND(AVG(salary), 2) as avg_salary,
MIN(salary) as min_salary,
MAX(salary) as max_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;
```
## Resumen
- Funciones de agregación: COUNT, SUM, AVG, MIN, MAX
- GROUP BY agrupa filas para agregación
- HAVING filtra resultados agrupados (WHERE filtra filas)
- COUNT(*) cuenta todas las filas; COUNT(columna) cuenta no-NULL
- Las funciones de agregación ignoran NULL (excepto COUNT(*))
- Usa ORDER BY después de GROUP BY para resultados ordenados
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →