Tengo esta tabla de muchos a muchos de muestra para estudiantes y asignaturas en una universidad
student | subject ----------------- James | English James | Physics Paul | Mathematics Paul | English Paul | English Paul | French Jake | French Jake | Mathematics Paul | EnglishNecesito saber la consulta SQL para obtener el recuento de materias para cada estudiante como
student | # of subjects ------------------------ James | 2 Paul | 3 Jake | 2Todo lo que necesita es GROUP BY student y COUNT(DISTINCT) :
SELECT student, COUNT(DISTINCT subject) AS "# of subjects" FROM students_subjects GROUP BY student;Necesitas agrupar
CREATE TABLE `student_subject` ( `name` varchar(256) DEFAULT NULL, `subject` varchar(256) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `student_subject` -- INSERT INTO `student_subject` (`name`, `subject`) VALUES ('James', 'English'), ('James', 'Physics'), ('Paul', 'Mathematics'), ('Paul', 'English'), ('Paul', 'English'), ('Paul', 'French'), ('Jake', 'Mathematics'), ('Jake', 'French'); SELECT name, COUNT(distinct subject) AS "subject_count" FROM student_subject GROUP BY name,subject order by name desc #########output############## Name subject_count ('Jake' , 2), ('James', 2), ('Paul' , 3);SELECT student, count(*) FROM table GROUP BY student