Lección 24: JOIN + GROUP BY

Aquí nacen los reportes empresariales: ventas por cliente, por categoría, por mes, etc.

Ventas totales por cliente

SELECT c.nombre AS cliente,
       COUNT(v.id_venta) AS total_ventas,
       SUM(v.total) AS monto_total
FROM clientes c
INNER JOIN ventas v
  ON v.id_cliente = c.id_cliente
GROUP BY c.nombre
ORDER BY monto_total DESC;

Clientes con monto_total >= 500 (HAVING)

SELECT c.nombre AS cliente, SUM(v.total) AS monto_total
FROM clientes c
INNER JOIN ventas v
  ON v.id_cliente = c.id_cliente
GROUP BY c.nombre
HAVING SUM(v.total) >= 500
ORDER BY monto_total DESC;

Top 5 clientes por monto (SQL Server: TOP)

SELECT TOP 5 c.nombre AS cliente, SUM(v.total) AS monto_total
FROM clientes c
INNER JOIN ventas v
  ON v.id_cliente = c.id_cliente
GROUP BY c.nombre
ORDER BY monto_total DESC;