GROUP BY Me los agrupa todos, no delimita

[bluesky777]

Hola a todos, hace tiempo hice un tema donde intentaba hacer lo mismo, pero no he podido, me disculpan si tenia que continuar con el tema viejo. Debo sacar los promedios y los puestos de los alumnos, el periodo pasado tocó hacerlo manualmente porque no fué capaz (En este no quiero que sea así).

Creé vista como me aconsejaron aqui:

Código:

CREATE or replace VIEW `vistanotascalculadas` AS select `n`.`idAlumno` AS`idAlumno`,
`a`.`NombresAlum` AS `NombresAlum`,`mg`.`idMaterGrupo` AS `idMaterGrupo`,
`c`.`idCompet` AS `idCompet`,`i`.`Indicador` AS `Indicador`,`i`.`idIndic` AS `idIndic`,
`ga`.`idPeriodo` AS `idPeriodo`,`g`.`idGrupo` AS `idGrupo`,
`g`.`NombreGrupo` AS `NombreGrupo`,
((((`c`.`PorcCompet`* `i`.`PorcIndic`) / 100) * `n`.`Nota` )/100)  AS `NotaDeMateria` 
from ((((((`tbnotas` `n` 
join `tbindicadores` `i`) 
join `tbcompetencias` `c`) 
join `tbmateriagrupo` `mg`) 
join `tbalumnos` `a`) 
join `tbgrupoalumnos` `ga`) 
join `tbgrupos` `g`) 
where ((`n`.`idIndic` = `i`.`idIndic`) 
and (`i`.`CompetenciaIndic` = `c`.`idCompet`) 
and (`mg`.`idMaterGrupo` = `c`.`MateriaGrupoCompet`) 
and (`a`.`idAlum` = `n`.`idAlumno`) 
and (`mg`.`idGrupo` = `g`.`idGrupo`) 
and (`g`.`idGrupo` = `ga`.`idGrupo`) 
and (`ga`.`idAlumno` = `a`.`idAlum`)) 
order by `n`.`idAlumno`

NotaDeMateria es lo que necesito que se sume por materia (idMaterGrupo), y luego lo promedio para sacar el puntaje final del alumno. Lo hago así en php:

Código:

$sqlMat="select idAlumno, NombresAlum, idMaterGrupo, SUM(NotaDeMateria) AS PromMater 
from `vistanotascalculadas`
where idAlumno=".$rSqlAl['idAlumno']." and idPeriodo=".$_SESSION['PeriodoUsu']."
GROUP BY idMaterGrupo";

Aquí es donde me agrupa sumando todas las NotaDeMateria sin hacer distinción de periodo Sin embargo me doy cuenta que no debo tener una vista así pues cada periodo se está multiplicando de forma terrible con todas las notas existentes. Así que opté por hacer una vista por periodo:

Código:

CREATE or replace VIEW `vistanotascalculadasper1` AS 
SELECT `n`.`idAlumno` AS`idAlumno`, `a`.`NombresAlum` AS `NombresAlum`,
`mg`.`idMaterGrupo` AS `idMaterGrupo`, `c`.`idCompet` AS `idCompet`,`i`.`Indicador` AS `Indicador`,
`i`.`idIndic` AS `idIndic`, `ga`.`idPeriodo` AS `idPeriodo`,`g`.`idGrupo` AS `idGrupo`, 
`g`.`NombreGrupo` AS `NombreGrupo`, 
((((`c`.`PorcCompet`* `i`.`PorcIndic`) / 100) * `n`.`Nota` )/100) AS `NotaDeMateria` 
from ((((((`tbnotas` `n` 
join `tbindicadores` `i`) 
join `tbcompetencias` `c`) 
join `tbmateriagrupo` `mg`) 
join `tbalumnos` `a`) 
join `tbgrupoalumnos` `ga`) 
join `tbgrupos` `g`) 
where ((`n`.`idIndic` = `i`.`idIndic`) 
and (`i`.`CompetenciaIndic` = `c`.`idCompet`) 
and (`mg`.`idMaterGrupo` = `c`.`MateriaGrupoCompet`) 
and (`a`.`idAlum` = `n`.`idAlumno`) 
and (`mg`.`idGrupo` = `g`.`idGrupo`) 
and (`g`.`idGrupo` = `ga`.`idGrupo`) 
and (`ga`.`idAlumno` = `a`.`idAlum`) 
) AND `idPeriodo`=1 
order by `n`.`idAlumno`

Luego con php:

Código:

$sqlMat="select idAlumno, NombresAlum, idMaterGrupo, SUM(NotaDeMateria) AS PromMater 
from `vistanotascalculadasper".$_SESSION['PeriodoUsu']."`
where idAlumno=".$rSqlAl['idAlumno']." 
GROUP BY idMaterGrupo";

vistanotascalculadasper1 no me hace distinción entre periodos tampoco, ¿Cómo hago para hacer una vista por periodo y sacar los puestos?(Esto es lo q necesito).

La primera vista, aunque ya no me parece una opción, me gustaría que me explicaran por qué no agrupa por periodo, pues la trabajé mucho y no entiendo porqué me agrupa sin tener en cuenta el periodo.

[bluesky777]

Ah, por si acaso les digo q tbgrupoalumnos tiene el alumno, grupo y periodo. En tbmateriagrupo están las materias de los tbgrupos; tbcompetencias tiene el id de la materia a la que pertenece, tbindicadores tiene el id de la competencia a la que pertenece; y tbnotas tiene: el codigo del estudiante, el id del indicador al que pertenece y la Nota. Muchas gracias espero seguir aprendiendo con ustedes.

[quimfv]

...
tbgrupoalumnos tiene el alumno, grupo y periodo

tbmateriagrupo están las materias de los tbgrupos

tbcompetencias tiene el id de la materia a la que pertenece

tbindicadores tiene el id de la competencia a la que pertenece

tbnotas tiene: el codigo del estudiante, el id del indicador al que pertenece y la Nota
...

Para poder ayudarte deberías postear los campos concretos de todas las tablas y sus relaciones, postea el resultado de

SHOW CREATE TABLE nombreTabla

para cada tabla.

Con lo que nos dices yo diria que en la tabla tbnotas falta el periodo para que esto pueda funcionar .... o es que los alumnos son distintos en cada perido?


Para que te diferencie Alumnos periodos y materias debes indicarlo en el GROUP BY....

GROUP BY idPeriodo,idAlumno,idMaterGrupo";

y entre las funciones de agregado esta AVG que directamente te darà la nota promedio del alumno....

12.15.1. GROUP BY (Aggregate) Functions

[bluesky777]

Hola, aqui están las tablas básicas, espero lo entiendan y de pronto le sirva a alguien.



Hasta el momento creo que no hace falta nada, pues el periodo lo define tbgrupoalumnos, aunque ahora que lo pienso no he tomado en cuenta que tbcompetencias tambien tiene el periodo, no sé si por ai vaya el maní.

Lo de group by lo he usado así, pero igual me suma todos sin hacer distinción de periodos, pero como dije, no habia tomado en cuenta el tbcompetencias.idPeriodo

Gracias por seguirme ayudando.

[gnzsoloyo]

La primera vista, aunque ya no me parece una opción, me gustaría que me explicaran por qué no agrupa por periodo, pues la trabajé mucho y no entiendo porqué me agrupa sin tener en cuenta el periodo.

Mira, uno de los problemas que tienes es que estás ordenando en una vista, cosa que no tiene mucho sentido a menos que uses la vista en "crudo", es decir, que la invoques como está y sn cambiar nada.
El ORDER BY y el GROUP BY son las cláusulas menos performánticas, y que sólo se deben usar donde y cuando se las necesite realmente.
Y no es en este punto.
¿Qué sentido tiene ordenar en una vista, para luego ordenar cuando llamas a la vista?

El segundo problema es que no conoces el funcionamiento del GROUP BY en MySQL, el cual tiene algunas diferencias bastante críticas.
Por empezar, cuando haces un agrupamiento simple sobre una tabla, es decir, sobre un sólo campo, tienes que tener en cuenta que MySQL simplificará el resultado y te devolverá el primer registro que encuentre con cada valor distinto sobre ese campo.
Este detalle implica que si tuvieses 30 valores de un campo, donde el mayor fuese el 7°, y el menor el primero, si agrupas directamente sobre otro campo, te devolverá el registro que primero encuentre: El del menor valor.
¿Se entiende?
Entonces, como el ORDER BY se aplica sobre el resultado de un GROUP BY (y no al revés), ese GROUP BY puede "esconder" valores antes del ordenamiento.
La única solución es:
1) La vista no debe ser ordenada.
2) Primer indicas el criterio de agrupamiento al invocar a la vista, incluyendo (como dice quimfv) todos los campos que actúan de datos base.
3) Ese SELECT debe generar una tabla derivada, a la cual se le aplica el ORDER BY.

En definitiva quedaría mas o menos así (ejemplo):

Código MySQL:

CREATE OR REPLACE VIEW vistanotascalculadas
AS
SELECT 
    `n`.`idAlumno`,
    `a`.`NombresAlum`,
    `mg`.`idMaterGrupo`,
    `c`.`idCompet`,
    `i`.`Indicador`,
    `i`.`idIndic`,
    `ga`.`idPeriodo`,
    `g`.`idGrupo`,
    `g`.`NombreGrupo`,
    ((((`c`.`PorcCompet`* `i`.`PorcIndic`) / 100) * `n`.`Nota` )/100)  `NotaDeMateria` 
FROM 
    tbnotas n INNER JOIN tbindicadores i ON `n`.`idIndic` = `i`.`idIndic`
    INNER JOIN `tbcompetencias` `c` ON `i`.`CompetenciaIndic` = `c`.`idCompet`
    INNER JOIN `tbmateriagrupo` `mg` ON `mg`.`idMaterGrupo` = `c`.`MateriaGrupoCompet` AND 
    INNER JOIN `tbalumnos` `a` ON `a`.`idAlum` = `n`.`idAlumno`
    INNER JOIN `tbgrupoalumnos` `ga` ON `ga`.`idAlumno` = `a`.`idAlum`
    INNER JOIN `tbgrupos` `g` ON `mg`.`idGrupo` = `g`.`idGrupo` AND `g`.`idGrupo` = `ga`.`idGrupo`;
 

Y luego la llamas:

Código MySQL:

SELECT 
    IDALUMNO, 
    NOMBRESALUM, 
    idGrupo,
    IDMATERGRUPO,
    PROMMATER
FROM
    (SELECT
        idAlumno, 
        NombresAlum, 
        idMaterGrupo, 
        idGrupo, 
        SUM(NotaDeMateria) PromMater 
    FROM `vistanotascalculadasper`
    GROUP BY IDALUMNO, idMaterGrupo) T1
ORDER BY idGrupo, idMaterGrupo;
 

Un detalle adicional: No tiene ningún sentido ni es buena performance crear una vista para cada sesión, persona, grupo, etc.
Es una pésima práctica que te puede llevar a perder el control de los objetos que hay en la base.
Lo que se hace es crear una vista con los datos necesarios para poder filtrarla y obtener el segmento de datos deseado.
¿Se comprende la idea?

[bluesky777]

Muchisimas gracias, estuve intentando aplicar todo lo que me estan diciendo, y por fin encontré que debo ser muy detallado a la hora de relacionar tablas, pues me traia una multiplicacion o notas que no eran del grupo. Estoy muy feliz, he aprendido mucho y lo mejor.... solucioné el problema, y es realmente gracias a toda la informacion que ustedes me dieron, mil gracias.

gnzsoloyo, workbench me creaba el order by, y por cuestiones de probar la consulta interna, y luego la externa, era necesario ponerlo o dejarlo, aunque sé que no sirve de nada en el resultado final.

Dios los guarde mucho.

[gnzsoloyo]

gnzsoloyo, workbench me creaba el order by (...)

Eso es algo que pasa con cualquier software de este tipo. Cuando usas los asistentes siempre le agregan cosas que son consideradas "estándar",pero que en realidad terminan siendo basura.
Lo que se suele hacer (al menos yo lo hago), es usar el asistente para acelerar la creación de la consulta y hacerlo de un modo "amigable", pero luego esa consulta obtenida jamás se debería usar como está, sino que hay que hacerle ajustes tales como borrarle el código irrelevante (los AS, algunos paréntesis, etc.), y quitarle lo que no necesita (como los ORDER BY, GROUP BY) cuando no es parte del diseño buscado.
Siempre hay que tocar algo del código obtenido. A fin de cuentas, no pretenderás que algo estándar sirva sin correcciones para usos específicos...