Clever Geek Handbook

SQL count second column for every unique row of first column

I have this table for many, many students for college students and university students

student | subject
-----------------
James   | English
James   | Physics
Paul    | Mathematics
Paul    | English
Paul    | English
Paul    | French
Jake    | French
Jake    | Mathematics
Paul    | English

I need to know a SQL query to get the subject count for each student, for example

student | # of subjects
------------------------
James   |   2
Paul    |   3
Jake    |   2
+3


source to share


5 answers


All you need is GROUP BY student

and COUNT(DISTINCT)

:



SELECT student, COUNT(DISTINCT subject) AS "# of subjects" 
FROM students_subjects
GROUP BY student;
+2


source


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);
+2


source 


SELECT student, count(*) 
FROM table
GROUP BY student
-2


source


You need to group the selection by the student:

SELECT student, count(*) as NumberOfSubjects
FROM table_name
GROUP BY student
-2


source 


Select student , count(subject) from Table Group by student
-3


source






More articles:


All Articles