FUNCIONES, GROUP BY y HAVING

🧮 FUNCIONES DE AGREGACIÓN

💡 Las funciones permiten resumir datos numéricos o contar registros.

• MAX()
• MIN()
• AVG()
• SUM()
• COUNT()

Ejemplo general

SELECT 
   MAX(invoice_total) AS alto,
   MIN(invoice_total) AS menor,
   AVG(invoice_total) AS media,
   SUM(invoice_total) AS total, 
   COUNT(invoice_total) AS numero_de_entradas,
   COUNT(payment_date) AS cuentas_de_pago
FROM invoices;

📤 Resultado:

alto	menor	media	total	numero_de_entradas	cuentas_de_pago
1200	50	380	15200	40	35

COUNT(*) vs COUNT(columna)

SELECT 
   MAX(invoice_total) AS alto,
   MIN(invoice_total) AS menor,
   AVG(invoice_total) AS media,
   SUM(invoice_total) AS total, 
   COUNT(*) AS total_datos
FROM invoices;

📤 Resultado:

alto	menor	media	total	total_datos
1200	50	380	15200	40

Operaciones dentro de funciones

SELECT 
   MAX(invoice_total) AS alto,
   MIN(invoice_total) AS menor,
   AVG(invoice_total) AS media,
   SUM(invoice_total * 1.1) AS total
FROM invoices;

📤 Resultado:

alto	menor	media	total
1200	50	380	16720

Filtrar con WHERE

SELECT 
   MAX(invoice_total) AS alto,
   MIN(invoice_total) AS menor,
   AVG(invoice_total) AS media,
   SUM(invoice_total * 1.1) AS total
FROM invoices
WHERE invoice_date > '2019-07-01';

📤 Resultado:

alto	menor	media	total
1100	100	420	10230

Contar clientes distintos

SELECT 
   MAX(invoice_total) AS alto,
   MIN(invoice_total) AS menor,
   AVG(invoice_total) AS media,
   COUNT(DISTINCT client_id) AS total
FROM invoices;

📤 Resultado:

alto	menor	media	total_clientes
1200	50	380	15

Ejercicio Funciones 1_p – Pruebe algunos comandos. Ejercicio Funciones 1

📈 GROUP BY

💡 Agrupa filas que comparten un valor común para aplicar funciones agregadas.

Sin GROUP BY

SELECT 
   SUM(invoice_total) AS total_sales
FROM invoices;

📤 Resultado:

total_sales
15200

Agrupar por cliente

SELECT 
   client_id,
   SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id;

📤 Resultado:

client_id	total_sales
1	2500
2	1800
3	4200

Ordenar resultados agrupados

SELECT 
   client_id,
   SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
ORDER BY total_sales DESC;

📤 Resultado:

client_id	total_sales
3	4200
1	2500
2	1800

Filtrar antes de agrupar

SELECT 
   client_id,
   SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id
ORDER BY total_sales DESC;

📤 Resultado:

client_id	total_sales
1	2200
4	2000
5	1500

GROUP BY con JOIN

SELECT 
   state,
   city,
   SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients USING (client_id)
GROUP BY state, city;

📤 Resultado:

state	city	total_sales
CA	San Diego	4800
TX	Dallas	2300
NY	Albany	1800

Ejercicio Funciones 2_p Ejercicio Funciones 2

🧮 HAVING

💡 HAVING se usa para filtrar resultados después de agrupar, cuando WHERE ya no aplica.

Error común

SELECT 
   client_id,
   SUM(invoice_total) AS total_sales
FROM invoices 
WHERE total_sales > 500
GROUP BY client_id;

⚠️ Error: total_sales no existe aún, porque se calcula después del GROUP BY.

Solución con HAVING

SELECT 
   client_id,
   SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales > 500;

📤 Resultado:

client_id	total_sales
1	2500
3	4200
5	510

HAVING con múltiples condiciones

SELECT 
   client_id,
   SUM(invoice_total) AS total_sales,
   COUNT(*) AS number_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 AND number_invoices > 5;

📤 Resultado:

client_id	total_sales	number_invoices
3	4200	6
5	510	7

Ejercicio Funciones 3_p Ejercicio Funciones 3

🚫 Ejemplo incorrecto

SELECT 
   client_id,
   SUM(invoice_total) AS total_sales,
   COUNT(*) AS number_invoices
FROM invoices
GROUP BY client_id
HAVING payment_date;

⚠️ No funciona, payment_date no está en el SELECT ni es una agregación.

✅ Forma correcta

SELECT 
   client_id,
   SUM(invoice_total) AS total_sales,
   COUNT(*) AS number_invoices
FROM invoices
WHERE payment_date > 10
GROUP BY client_id;

📤 Resultado:

client_id	total_sales	number_invoices
2	1800	3
4	2300	5

📊 Resumen final

Función	Descripción
MAX()	Valor máximo
MIN()	Valor mínimo
AVG()	Promedio
SUM()	Suma
COUNT()	Conteo
GROUP BY	Agrupa resultados
HAVING	Filtra resultados agrupados