SELECT A1.CCT, A1.AUX10, COUNT(A2.OP1) AS ASPIRANTES
FROM escueladetalle A1, aspirante A2
WHERE (
mid( A1.CCT, 3, 3 ) = 'EJN'
OR mid(A1.CCT, 3, 3 ) = 'DJN'
OR mid(A1. CCT, 3, 3 ) = 'PJN'
OR mid(A1.CCT, 3, 3 ) = 'DPR'
OR mid(A1.CCT, 3, 3 ) = 'EPR'
OR mid(A1.CCT, 3, 3 ) = 'PPR'
OR mid(A1.CCT, 3, 3 ) = 'DES'
OR mid(A1.CCT, 3, 3 ) = 'DST'
OR mid(A1.CCT, 3, 3 ) = 'DTV'
OR mid(A1.CCT, 3, 3 ) = 'EES'
OR mid(A1.CCT, 3, 3 ) = 'EST'
OR mid(A1.CCT, 3, 3 ) = 'ETV'
OR mid(A1.CCT, 3, 3 ) = 'PES'
OR mid(A1.CCT, 3, 3 ) = 'PST'
OR mid(A1.CCT, 3, 3 ) = 'PTV'
OR mid(A1.CCT, 3, 3 ) = 'SES'
) AND (A1.CCT = A2.OP1) GROUP BY A2.OP1



SELECT A3.serreg, A3.nivel_id, A3.zonaescola, A3.ct_id, A1.CCT, A1.AUX10, COUNT(A2.OP1) AS ASPIRANTES
FROM escueladetalle A1, aspirante A2, cambios_centro_trabajo A3
WHERE (
mid( A3.ct_id, 3, 3 ) = 'EJN'
OR mid( A3.ct_id, 3, 3 ) = 'DJN'
OR mid( A3.ct_id, 3, 3 ) = 'PJN'
OR mid( A3.ct_id, 3, 3 ) = 'DPR'
OR mid( A3.ct_id, 3, 3 ) = 'EPR'
OR mid( A3.ct_id, 3, 3 ) = 'PPR'
OR mid( A3.ct_id, 3, 3 ) = 'DES'
OR mid( A3.ct_id, 3, 3 ) = 'DST'
OR mid( A3.ct_id, 3, 3 ) = 'DTV'
OR mid( A3.ct_id, 3, 3 ) = 'EES'
OR mid( A3.ct_id, 3, 3 ) = 'EST'
OR mid( A3.ct_id, 3, 3 ) = 'ETV'
OR mid( A3.ct_id, 3, 3 ) = 'PES'
OR mid( A3.ct_id, 3, 3 ) = 'PST'
OR mid( A3.ct_id, 3, 3 ) = 'PTV'
OR mid( A3.ct_id, 3, 3 ) = 'SES'
)
AND (
A3.status_ct_id = '1'
OR A3.status_ct_id = '4'
) AND (A1.CCT = A2.OP1 OR A1.CCT = A3.ct_id) 
GROUP BY OP1
ORDER BY A3.serreg, A3.nivel_id, A3.zonaescola, A3.ct_id