how to fetch data from two tables in mysql?

Question

I have to find out the output as class name and number of students in each class? My both tables are-

CREATE TABLE student(Fields_ID INT, Name VARCHAR(20));
        INSERT INTO student(Fields_ID,Name) VALUES(30,'JYOTI');
        INSERT INTO student(Fields_ID,Name) VALUES(31,'KIRTI');
        INSERT INTO student(Fields_ID,Name) VALUES(32,'YOGITA');
        INSERT INTO student(Fields_ID,Name) VALUES(33,'RASHMI');
        INSERT INTO student(Fields_ID,Name) VALUES(34,'NUPUR');
SELECT * FROM student;

CREATE TABLE class(Fields_ID INT, Name VARCHAR(20));
        INSERT INTO class(Fields_ID,Name) VALUES(30,'FIRST');
        INSERT INTO class(Fields_ID,Name) VALUES(31,'SECOND');
        INSERT INTO class(Fields_ID,Name) VALUES(32,'THIRD');
        INSERT INTO class(Fields_ID,Name) VALUES(33,'FOURTH');
        INSERT INTO class(Fields_ID,Name) VALUES(34,'FIFTH');
SELECT * FROM class;

I was trying to return the required out from the following code but it does not return same. Any idea why it is not returning the right values.I am a beginner in MySql so I am unable to find out the problem.

SELECT class.Name , COUNT(student.name)
From class INNER JOIN student
    ON class.Fields_ID=student.Fields_ID;

Answer 1

Using aggregate functions you should group them like GROUP BY class.Name so you will get the count of students in each class,other wise you will get a single row not the results per group i.e students per class

SELECT class.Name , COUNT(student.name)
From class INNER JOIN student
    ON class.Fields_ID=student.Fields_ID
GROUP BY class.Name;

GROUP BY (Aggregate) Functions

See fiddle demo