I have this sample many-to-many table for students & subjects in a university
student | subject
-----------------
James | English
James | Physics
Paul | Mathematics
Paul | English
Paul | English
Paul | French
Jake | French
Jake | Mathematics
Paul | English
I need to know the SQL query for getting the count of subjects for each student like
student | # of subjects
------------------------
James | 2
Paul | 3
Jake | 2
All you need is GROUP BY student and COUNT(DISTINCT):
SELECT student, COUNT(DISTINCT subject) AS "# of subjects"
FROM students_subjects
GROUP BY student;
You need to group
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