-- Creacion de la tabla activos
DROP TABLE colonia_activos
SELECT c1.colonia, COUNT(DISTINCT c1.id) AS activos
INTO colonia_activos
FROM clientes AS c1
INNER JOIN contrato AS c2
ON c1.id = c2.cliente
WHERE c2.cerrado = 0
GROUP BY c1.colonia
-- Creacion de la tabla inactivos
DROP TABLE colonia_inactivos
SELECT c1.colonia, COUNT(DISTINCT c1.id) AS inactivos
INTO colonia_inactivos
FROM clientes AS c1
INNER JOIN contrato AS c2
ON c1.id = c2.cliente
WHERE c2.cliente NOT IN
(SELECT cliente FROM contrato WHERE cerrado = 0)
GROUP BY c1.colonia
-- Creacion de la tabla totales
DROP TABLE colonia_totales
SELECT DISTINCT c1.colonia, c2.activos, c3.inactivos
INTO colonia_totales
FROM clientes AS c1
LEFT JOIN colonia_activos AS c2
ON c1.colonia = c2.colonia
LEFT JOIN colonia_inactivos AS c3
ON c1.colonia = c3.colonia
ORDER BY c1.colonia
UPDATE colonia_totales
SET activos = 0
WHERE activos IS NULL
UPDATE colonia_totales
SET inactivos = 0
WHERE inactivos IS NULL
SELECT * FROM colonia_totales