repetidos - MySQL Query-Obteniendo registros faltantes cuando usa group-by

sql contar registros agrupados

Question

Tengo una consulta :

select score, count(1) as ''NumStudents'' from testresults where testid = ''mytestid''
group by score order by score

donde testresults table contiene las actuaciones de los estudiantes en una prueba. Un resultado de muestra tiene el siguiente aspecto, suponiendo que la puntuación máxima de la prueba es 10.

puntaje, NumStudents

0 10
1 20
2 12
3 5
5 34
..
10 23

Como puede ver, esta consulta no devuelve ningún registro de calificaciones que ningún alumno haya calificado. Por ej. nadie obtuvo 4/10 en la prueba y no hay registros para la puntuación = 4 en la salida de la consulta.

Me gustaría cambiar la consulta para que pueda obtener estos registros faltantes con 0 como valor para el campo NumStudents. Para que mi salida final tenga un máximo de + 1 registros, uno por cada puntuación posible.

Algunas ideas ?

EDITAR:

La base de datos contiene varias pruebas y las marcas máximas para la prueba son parte de la definición de prueba. Por lo tanto, no es posible tener una nueva tabla para almacenar todos los puntajes posibles. En el sentido de que cada vez que creo una nueva prueba con una nueva puntuación máxima, debo asegurarme de que la nueva tabla también se modifique para que contenga estas puntuaciones.

Answer 1

¿MySQL soporta funciones de devolución de conjunto? Las versiones recientes de PostgreSQL tienen una función, generate_series(start, stop) que produce el valor start en la primera fila, start+1 en el segundo, y así sucesivamente hasta stop en la fila stop th. La ventaja de esto es que puede poner esta función en una subselección en la cláusula FROM y luego unirse a ella, en lugar de crear y completar una tabla y unirla a la sugerida por le dorfier y Bill Karwin.

Answer 2

Al igual que un ejercicio mental, se me ocurrió esto para generar una secuencia en MySQL. Siempre que el número de tablas en todas las bases de datos en el cuadrado de la casilla sea menor que la longitud total de la secuencia, funcionará. No lo recomendaría para producción;)

SELECT @n:=@n+1 as n from (select @n:=-1) x, Information_Schema.Tables y, Information_Schema.Tables WHERE @n<20; /* sequence from 0 to 20 inclusive */

Answer 3

La forma más obvia sería crear una tabla llamada "Puntuaciones" y la combinación externa izquierda de su tabla.

SELECCIONAR en s.core, COUNT (1) AS scoreCount
FROM puntuación AS s
IZQUIERDA UNIÓN EXTERNA testScores AS ts
ON s.score = ts.score
GROUP BY s.score

Si no quieres crear la tabla, podrías usar

SELECCIONAR
1 como puntaje, SUMA (CASO CUANDO ts.score = 1 THEN 1 ELSE 0 END) COMO scoreCount,
2 como puntaje, SUMA (CASO CUANDO ts.score = 2 THEN 1 ELSE 0 END) COMO scoreCount,
3 como puntaje, SUMA (CASO CUANDO ts.score = 3 THEN 1 ELSE 0 END) COMO scoreCount,
4 como puntaje, SUMA (CASO CUANDO ts.score = 4 THEN 1 ELSE 0 END) AS scoreCount,
... 10 como puntaje, SUMA (CASO CUANDO ts.score = 10 THEN 1 ELSE 0 END) AS scoreCount
FROM testScores AS ts

Answer 4

SQL funciona bien con conjuntos de valores de datos en la base de datos, pero no tan bueno en conjuntos de valores de datos que no están en la base de datos.

La mejor solución es mantener una tabla pequeña para los valores que necesita para variar:

CREATE TABLE ScoreValues (score int);
INSERT INTO ScoreValues (score) 
  VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

Dado su comentario de que define las marcas máximas de una prueba en otra tabla, puede unirse a esa tabla de la siguiente manera, siempre y cuando ScoreValues tenga valores al menos tan altos o más altos que las marcas máximas de la prueba máxima:

SELECT v.score, COUNT(tr.score) AS ''NumStudents''
FROM ScoreValues v 
  JOIN Tests t ON (v.score <= t.maxmarks)
  LEFT OUTER JOIN TestResults tr ON (v.score = tr.score AND t.testid = tr.testid)
WHERE t.testid = ''mytestid''
GROUP BY v.score;