Buenas tardes, realmente me considero algo newbie pero me salio este problema y le he buscado pero no se por donde llegarle,tengo una tabla donde atrapo examenes en linea donde quien aplica el examen califica a otros compañeros, atrapo numeros enteros en la tabla, las columnas son:
usuaio,P1,P2,P3,P4,P5, seccion, tipo_empleado, aplicado
usuario = quien aplica el examen
P1,...P5 = respuestas
seccion = en que seccion del examen se encuentran
tipo_empleado = que tipo de empleado es quien esta ejecutando el examen
aplicado = a quien se le esta evaluando en el examen
Ahora un solo usuario ingresa 4 secciones contestando las 5 preguntas, y tengo 400 usuarios, necesito obtener un query que me permita promediar los resultados:
- por pregunta,
- por pregunta y por seccion
- por pregunta, por seccion y por tipo_empleado
Yo lo habia hemo manual pero al final el query me indica que no puedo consultar mas de 255 tablas por consulta y estoy usando al rededor de 380, debe de haber una forma de hacerlo mucho mas simple, no se si me puedan ayudar, adjunto el codigo
SELECT
aplicado,
-- Inicia ** PROMEDIO POR PREGUNTA
AVG("P1") as [Prom P1],
AVG("P2") as [Prom P2],
AVG("P3") as [Prom P3],
AVG("P4") as [Prom P4],
AVG("P5") as [Prom P5],
-- Inicia ** PROMEDIO POR PREGUNTA POR seccion
(select AVG("P1") from DATOST where seccion='ma') as [P1-MA],
(select AVG("P2") from DATOST where seccion='ma') as [P2-MA],
(select AVG("P3") from DATOST where seccion='ma') as [P3-MA],
(select AVG("P4") from DATOST where seccion='ma') as [P4-MA],
(select AVG("P5") from DATOST where seccion='ma') as [P5-MA],
(select AVG("P1") from DATOST where seccion='mb') as [P1-MB],
(select AVG("P2") from DATOST where seccion='mb') as [P2-MB],
(select AVG("P3") from DATOST where seccion='mb') as [P3-MB],
(select AVG("P4") from DATOST where seccion='mb') as [P4-MB],
(select AVG("P5") from DATOST where seccion='mb') as [P5-MB],
(select AVG("P1") from DATOST where seccion='mc') as [P1-MC],
(select AVG("P2") from DATOST where seccion='mc') as [P2-MC],
(select AVG("P3") from DATOST where seccion='mc') as [P3-MC],
(select AVG("P4") from DATOST where seccion='mc') as [P4-MC],
(select AVG("P5") from DATOST where seccion='mc') as [P5-MC],
(select AVG("P1") from DATOST where seccion='md') as [P1-MD],
(select AVG("P2") from DATOST where seccion='md') as [P2-MD],
(select AVG("P3") from DATOST where seccion='md') as [P3-MD],
(select AVG("P1") from DATOST where seccion='me') as [P1-ME],
(select AVG("P2") from DATOST where seccion='me') as [P2-ME],
(select AVG("P3") from DATOST where seccion='me') as [P3-ME],
(select AVG("P1") from DATOST where seccion='mf') as [P1-MF],
(select AVG("P2") from DATOST where seccion='mf') as [P2-MF],
(select AVG("P3") from DATOST where seccion='mf') as [P3-MF],
(select AVG("P1") from DATOST where seccion='mg') as [P1-MG],
(select AVG("P2") from DATOST where seccion='mg') as [P2-MG],
(select AVG("P3") from DATOST where seccion='mg') as [P3-MG],
(select AVG("P1") from DATOST where seccion='mh') as [P1-MH],
(select AVG("P2") from DATOST where seccion='mh') as [P2-MH],
(select AVG("P3") from DATOST where seccion='mh') as [P3-MH],
(select AVG("P4") from DATOST where seccion='mh') as [P4-MH],
(select AVG("P5") from DATOST where seccion='mh') as [P5-MH],
(select AVG("P1") from DATOST where seccion='mi') as [P1-MI],
(select AVG("P2") from DATOST where seccion='mi') as [P2-MI],
(select AVG("P3") from DATOST where seccion='mi') as [P3-MI],
(select AVG("P4") from DATOST where seccion='mi') as [P4-MI],
(select AVG("P5") from DATOST where seccion='mi') as [P5-MI],
(select AVG("P1") from DATOST where seccion='mj') as [P1-MJ],
(select AVG("P2") from DATOST where seccion='mj') as [P2-MJ],
(select AVG("P3") from DATOST where seccion='mj') as [P3-MJ],
(select AVG("P4") from DATOST where seccion='mj') as [P4-MJ],
(select AVG("P5") from DATOST where seccion='mj') as [P5-MJ],
-- Inicia ** PROMEDIO POR PREGUNTA POR seccion POR tipo_empleado
--INICIA ma
(select AVG("P1") from DATOST where (seccion='ma' and tipo_empleado='VISITA')) as [P1-MA-VISITA],
(select AVG("P1") from DATOST where (seccion='ma' and tipo_empleado='PRINCIPAL')) as [P1-MA-PRINCIPAL],
(select AVG("P1") from DATOST where (seccion='ma' and tipo_empleado='INTERMEDIARIO')) as [P1-MA-INTERMEDIARIO],
(select AVG("P1") from DATOST where (seccion='ma' and tipo_empleado='MANDATARIO')) as [P1-MA-MANDATARIO],
(select AVG("P1") from DATOST where (seccion='ma' and tipo_empleado='INMEDIATO SUP')) as [P1-MA-INMEDIATO SUP],
(select AVG("P1") from DATOST where (seccion='ma' and tipo_empleado='INMEDIATO INF')) as [P1-MA-EL_MISMO],
(select AVG("P2") from DATOST where (seccion='ma' and tipo_empleado='VISITA')) as [P2-MA-VISITA],
(select AVG("P2") from DATOST where (seccion='ma' and tipo_empleado='PRINCIPAL')) as [P2-MA-PRINCIPAL],
(select AVG("P2") from DATOST where (seccion='ma' and tipo_empleado='INTERMEDIARIO')) as [P2-MA-INTERMEDIARIO],
(select AVG("P2") from DATOST where (seccion='ma' and tipo_empleado='MANDATARIO')) as [P2-MA-MANDATARIO],
(select AVG("P2") from DATOST where (seccion='ma' and tipo_empleado='INMEDIATO SUP')) as [P2-MA-INMEDIATO SUP],
(select AVG("P2") from DATOST where (seccion='ma' and tipo_empleado='INMEDIATO INF')) as [P2-MA-EL_MISMO],
(select AVG("P3") from DATOST where (seccion='ma' and tipo_empleado='VISITA')) as [P3-MA-VISITA],
(select AVG("P3") from DATOST where (seccion='ma' and tipo_empleado='PRINCIPAL')) as [P3-MA-PRINCIPAL],
(select AVG("P3") from DATOST where (seccion='ma' and tipo_empleado='INTERMEDIARIO')) as [P3-MA-INTERMEDIARIO],
(select AVG("P3") from DATOST where (seccion='ma' and tipo_empleado='MANDATARIO')) as [P3-MA-MANDATARIO],
(select AVG("P3") from DATOST where (seccion='ma' and tipo_empleado='INMEDIATO SUP')) as [P3-MA-INMEDIATO SUP],
(select AVG("P3") from DATOST where (seccion='ma' and tipo_empleado='INMEDIATO INF')) as [P3-MA-EL_MISMO],
(select AVG("P4") from DATOST where (seccion='ma' and tipo_empleado='VISITA')) as [P4-MA-VISITA],
(select AVG("P4") from DATOST where (seccion='ma' and tipo_empleado='PRINCIPAL')) as [P4-MA-PRINCIPAL],
(select AVG("P4") from DATOST where (seccion='ma' and tipo_empleado='INTERMEDIARIO')) as [P4-MA-INTERMEDIARIO],
(select AVG("P4") from DATOST where (seccion='ma' and tipo_empleado='MANDATARIO')) as [P4-MA-MANDATARIO],
(select AVG("P4") from DATOST where (seccion='ma' and tipo_empleado='INMEDIATO SUP')) as [P4-MA-INMEDIATO SUP],
(select AVG("P4") from DATOST where (seccion='ma' and tipo_empleado='INMEDIATO INF')) as [P4-MA-EL_MISMO],
(select AVG("P5") from DATOST where (seccion='ma' and tipo_empleado='VISITA')) as [P5-MA-VISITA],
(select AVG("P5") from DATOST where (seccion='ma' and tipo_empleado='PRINCIPAL')) as [P5-MA-PRINCIPAL],
(select AVG("P5") from DATOST where (seccion='ma' and tipo_empleado='INTERMEDIARIO')) as [P5-MA-INTERMEDIARIO],
(select AVG("P5") from DATOST where (seccion='ma' and tipo_empleado='MANDATARIO')) as [P5-MA-MANDATARIO],
(select AVG("P5") from DATOST where (seccion='ma' and tipo_empleado='INMEDIATO SUP')) as [P5-MA-INMEDIATO SUP],
(select AVG("P5") from DATOST where (seccion='ma' and tipo_empleado='INMEDIATO INF')) as [P5-MA-EL_MISMO],
--INICIA mb
(select AVG("P1") from DATOST where (seccion='mb' and tipo_empleado='VISITA')) as [P1-MB-VISITA],
(select AVG("P1") from DATOST where (seccion='mb' and tipo_empleado='PRINCIPAL')) as [P1-MB-PRINCIPAL],
(select AVG("P1") from DATOST where (seccion='mb' and tipo_empleado='INTERMEDIARIO')) as [P1-MB-INTERMEDIARIO],
(select AVG("P1") from DATOST where (seccion='mb' and tipo_empleado='MANDATARIO')) as [P1-MB-MANDATARIO],
(select AVG("P1") from DATOST where (seccion='mb' and tipo_empleado='INMEDIATO SUP')) as [P1-MB-INMEDIATO SUP],
(select AVG("P1") from DATOST where (seccion='mb' and tipo_empleado='INMEDIATO INF')) as [P1-MB-EL_MISMO],
(select AVG("P2") from DATOST where (seccion='mb' and tipo_empleado='VISITA')) as [P2-MB-VISITA],
(select AVG("P2") from DATOST where (seccion='mb' and tipo_empleado='PRINCIPAL')) as [P2-MB-PRINCIPAL],
(select AVG("P2") from DATOST where (seccion='mb' and tipo_empleado='INTERMEDIARIO')) as [P2-MB-INTERMEDIARIO],
(select AVG("P2") from DATOST where (seccion='mb' and tipo_empleado='MANDATARIO')) as [P2-MB-MANDATARIO],
(select AVG("P2") from DATOST where (seccion='mb' and tipo_empleado='INMEDIATO SUP')) as [P2-MB-INMEDIATO SUP],
(select AVG("P2") from DATOST where (seccion='mb' and tipo_empleado='INMEDIATO INF')) as [P2-MB-EL_MISMO],
(select AVG("P3") from DATOST where (seccion='mb' and tipo_empleado='VISITA')) as [P3-MB-VISITA],
(select AVG("P3") from DATOST where (seccion='mb' and tipo_empleado='PRINCIPAL')) as [P3-MB-PRINCIPAL],
(select AVG("P3") from DATOST where (seccion='mb' and tipo_empleado='INTERMEDIARIO')) as [P3-MB-INTERMEDIARIO],
(select AVG("P3") from DATOST where (seccion='mb' and tipo_empleado='MANDATARIO')) as [P3-MB-MANDATARIO],
(select AVG("P3") from DATOST where (seccion='mb' and tipo_empleado='INMEDIATO SUP')) as [P3-MB-INMEDIATO SUP],
(select AVG("P3") from DATOST where (seccion='mb' and tipo_empleado='INMEDIATO INF')) as [P3-MB-EL_MISMO],
(select AVG("P4") from DATOST where (seccion='mb' and tipo_empleado='VISITA')) as [P4-MB-VISITA],
(select AVG("P4") from DATOST where (seccion='mb' and tipo_empleado='PRINCIPAL')) as [P4-MB-PRINCIPAL],
(select AVG("P4") from DATOST where (seccion='mb' and tipo_empleado='INTERMEDIARIO')) as [P4-MB-INTERMEDIARIO],
(select AVG("P4") from DATOST where (seccion='mb' and tipo_empleado='MANDATARIO')) as [P4-MB-MANDATARIO],.......asi hasta mj
FROM DATOST
GROUP BY aplicado
ORDER BY aplicado
GRACIAS !!!