Actually, your way of storing the information in the database has a small standardization failure: you should use a table to record the data of the person evaluated, another for questions and another for their answers. Then you'd solve your problem with a simple group consultation. Another option would be to make that count from PHP, with a couple of loops that will pick up each user and their answers but, in SQL, it is not working like that. It could, but it would not be efficient for the programmer or the database engine.Assuming, because not the samples, you have exceeded the part where you generate the form and insert the data in the tables, I ask you how to create the tables, how to insert a test data and how to account for the stored answers:CREATE TABLE personas(
id_persona int AUTO_INCREMENT PRIMARY KEY,
nombre varchar(63),
apellido varchar(63),
grupo varchar(7),
email varchar(63)
);
CREATE TABLE preguntas(
id_pregunta int AUTO_INCREMENT PRIMARY KEY,
pregunta text,
opcionA text,
opcionB text,
opcionC text
);
CREATE TABLE respuestas(
id_respuesta int AUTO_INCREMENT PRIMARY KEY,
fecha_actual datetime,
id_persona int,
id_pregunta int,
opcion char(1),
FOREIGN KEY(id_pregunta) REFERENCES preguntas(id_pregunta)
);
Sample data insertion:INSERT INTO personas (nombre, apellido, grupo, email) VALUES
('JOSE FERNANDO','LUCIANO GARCIA','D','josep55@gmail.com');
INSERT INTO preguntas (pregunta, opcionA, opcionB, opcionC) VALUES
('¿Cuál de las siguientes actividades disfruta más?',
'Escuchar música','Ver películas','Bailar con buena música'),
('P2','A','B','C'),
('P3','A','B','C'),
('P4','A','B','C'),
('P5','A','B','C'),
('P6','A','B','C'),
('P7','A','B','C'),
('P8','A','B','C');
INSERT INTO respuestas (fecha_actual, id_persona, id_pregunta, opcion) VALUES
(NOW(),1,1,'A'),
(NOW(),1,2,'A'),
(NOW(),1,3,'B'),
(NOW(),1,4,'B'),
(NOW(),1,5,'A'),
(NOW(),1,6,'A'),
(NOW(),1,7,'A'),
(NOW(),1,8,'A');
Results accounting consultation:SELECT id_persona,opcion,COUNT(*)
FROM respuestas
GROUP BY 1,2
ORDER BY 1,2;
Results of the consultation for the proposed sample data:+------------+--------+----------+
| id_persona | opcion | COUNT(*) |
+------------+--------+----------+
| 1 | A | 6 |
| 1 | B | 2 |
+------------+--------+----------+
In a more compact way and combining with the test data, you could also show it:SELECT nombre,
apellido,
grupo,
email,
GROUP_CONCAT(CONCAT(
opcion,':',n_respuestas
) ORDER BY opcion) resultados
FROM personas JOIN (
SELECT id_persona,opcion,
COUNT(*) n_respuestas
FROM respuestas
GROUP BY 1,2
) c1 USING(id_persona)
GROUP BY 1,2,3,4;
+---------------+----------------+-------+-------------------+------------+
| nombre | apellido | grupo | email | resultados |
+---------------+----------------+-------+-------------------+------------+
| JOSE FERNANDO | LUCIANO GARCIA | D | josep55@gmail.com | A:6,B:2 |
+---------------+----------------+-------+-------------------+------------+
