How to Find Average Marks of Each Subject 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 subject-wise average marks scored by the students of a class. 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, we use a specific kind of query shown in the below demonstration. 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 SUBJECT and the average marks scored by the students in that subject 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 subject-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 SUBJECT,AVG(MARKS) AS "AVERAGE MARKS"
FROM STDMARKS GROUP BY SUBJECT;

Output: