Good, I have a db in mysql, I would like to count a certain value in many fields in the same table (not in a single field), for example in this case I have the table A = Fair S = Outstanding E = Excellent Table: notes
As you can see it is not a simple count, since there are several fields of notes for different students, I want for example to count the A, S and E in the different fields of all the students, thanks and I hope I have known explain.
This query tells you each note, and gives you a row name for each one. Not bad, right?
SELECT nota, COUNT(*) total
FROM (
SELECT nota1 as nota FROM estudiantes_20171010
UNION ALL
SELECT nota2 as nota FROM estudiantes_20171010
UNION ALL
SELECT nota3 as nota FROM estudiantes_20171010
UNION ALL
SELECT nota4 as nota FROM estudiantes_20171010
UNION ALL
SELECT nota5 as nota FROM estudiantes_20171010
) tbl_tmp
GROUP BY nota;
Although I would improve the design of the database, so we avoid headaches ... :) A simple table that saves id_estudiante , id_asignatura and nota would give an interesting respite to the table estudiantes , would allow you to tell what you want, even if there were thousands of types of notes, etc ...
I hope it's useful for you.
CREATE TABLE IF NOT EXISTS estudiantes_20171010
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(20), nota1 CHAR(1), nota2 CHAR(1),nota3 CHAR(1),nota4 CHAR(1),nota5 CHAR(1)
)ENGINE=INNODB;
INSERT INTO estudiantes_20171010 (nombre,nota1,nota2,nota3,nota4,nota5)
VALUES
('Estudiante1','E','S','S','A','S'),
('Estudiante2','A','S','A','E','S'),
('Estudiante3','S','E','S','S','S')
;
-- Datos de prueba
SELECT *
FROM estudiantes_20171010
;
-- Contando notas
SELECT nota, COUNT(*) total
FROM (
SELECT nota1 as nota FROM estudiantes_20171010
UNION ALL
SELECT nota2 as nota FROM estudiantes_20171010
UNION ALL
SELECT nota3 as nota FROM estudiantes_20171010
UNION ALL
SELECT nota4 as nota FROM estudiantes_20171010
UNION ALL
SELECT nota5 as nota FROM estudiantes_20171010
) tbl_tmp
GROUP BY nota;
- Test data
id nombre nota1 nota2 nota3 nota4 nota5
1 Estudiante1 E S S A S
2 Estudiante2 A S A E S
3 Estudiante3 S E S S S
- Counting
nota total
A 3
E 3
S 9
In this case, since all the characters to count occupy a position, a little trick can be done to count the characters.
Separate the query in two ..
First we have to get a single string per Student:
SELECT
CONCAT(nota_1,nota_2,...) as concatenado
FROM
TABLA
and then, we count for each row the amount of each occurrence, this is only valid because it is a letter! otherwise the function would be a little more complicated.
SELECT
LENGTH(concatenado) - LENGTH( REPLACE ( concatenado, "A", "") ) as NOTAS_A,
LENGTH(concatenado) - LENGTH( REPLACE ( concatenado, "E", "") ) as NOTAS_E,
LENGTH(concatenado) - LENGTH( REPLACE ( concatenado, "S", "") ) as NOTAS_S
FROM
TABLA_ANTERIOR
You can also use variables, for example:
// Se cuentan todas las S de la columna nota1 y se mete en variable
select count(*) from asignatura where Nota1='S' into @valor1;
// Se cuentan todas las S de la columna nota2 y se mete en variable
select count(*) from asignatura where Nota2='S' into @valor2;
// Hay que repetir las sentencias anteriores por cada columna de cada tabla.
// Se suman todas las variables y te da el número total de S por tabla.
select @valor1 + @valor2;
You can probably optimize this query by changing the Where, saying that it counts any of the S you find, but right now it's the first thing that comes to mind.