How to Find Average Marks of Each Student in SQL?

In SQL, sometimes we need to find the average value of a column based on another column of the table such as finding the student-wise average marks scored by him/her in all the subjects. This involves the use of the GROUP BY clause along with the AGGREGATE function like AVG. The same is depicted in the below article. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table of STDMARKS inside the database GeeksForGeeks. This table has 4 columns namely ROLL_NO, STUDENT_NAME, SUBJECT, and MARKS containing the roll number and name of the students and the subject name and marks scored by the student in the respective subject.

Query:

CREATE TABLE STDMARKS(
ROLL_NO INT,
STUDENT_NAME VARCHAR(10),
SUBJECT VARCHAR(10),
MARKS INT);

Output:

Step 4: Describe the structure of the table STDMARKS.

Query:

EXEC SP_COLUMNS STDMARKS;

Output:

Step 5: Insert 16 rows into the STDMARKS table.

Query:

INSERT INTO STDMARKS VALUES(1,'RAVI','C++',75);
INSERT INTO STDMARKS VALUES(1,'RAVI','ORACLE',84);
INSERT INTO STDMARKS VALUES(1,'RAVI','JAVA',95);
INSERT INTO STDMARKS VALUES(1,'RAVI','OS',62);
INSERT INTO STDMARKS VALUES(2,'VIVEK','C++',46);
INSERT INTO STDMARKS VALUES(2,'VIVEK','ORACLE',70);
INSERT INTO STDMARKS VALUES(2,'VIVEK','JAVA',55);
INSERT INTO STDMARKS VALUES(2,'VIVEK','OS',56);
INSERT INTO STDMARKS VALUES(3,'RAJ','C++',82);
INSERT INTO STDMARKS VALUES(3,'RAJ','ORACLE',84);
INSERT INTO STDMARKS VALUES(3,'RAJ','JAVA',74);
INSERT INTO STDMARKS VALUES(3,'RAJ','OS',92);
INSERT INTO STDMARKS VALUES(4,'MAHESH','C++',43);
INSERT INTO STDMARKS VALUES(4,'MAHESH','ORACLE',49);
INSERT INTO STDMARKS VALUES(4,'MAHESH','JAVA',68);
INSERT INTO STDMARKS VALUES(4,'MAHESH','OS',35);

Output:

Step 6: Display all the rows of the STDMARKS table.

Query:

SELECT * FROM STDMARKS;

Output:

Step 7: Display the ROLL_NO of the student and the average marks scored by that student in all subjects from the table STDMARKS. For achieving the aggregate value of a column according to another column, we need to use the GROUP BY clause along with the aggregate function such as AVG. The column mentioned after the GROUP BY clause is the basis for our output. The student-wise average marks are achieved in this manner.

Syntax:

SELECT COLUMN_NAME1,AVG(COLUMN_NAME2)
AS ALIAS FROM TABLE_NAME GROUP BY COLUMN_NAME1;

Query:

SELECT ROLL_NO,AVG(MARKS) AS "AVERAGE MARKS"
FROM STDMARKS GROUP BY ROLL_NO;

Output: