

How to Find Average Marks of Each Student in SQL?
source link: https://www.geeksforgeeks.org/how-to-find-average-marks-of-each-student-in-sql/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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:

Recommend
-
5
How to calculate the average score for each student advertisements I have a table "Register" with columns: ...
-
6
Calculation of the average inventory cost in SQL advertisements I want to compute inventory costs using average value, and I'm somewhat stuck here....
-
10
Heading back to school? Did you just graduate? The GitHub Education Stream Team (GEST) is sharing resources, tools, and more to help emerging developer...
-
10
SQL: Select the sum of each individual value of a column advertisements There is a column that can have several...
-
6
SQL Select a row for each category type advertisements I've the data in my table in following format. id | p...
-
16
How to calculate the average value of two lists for each row advertisements I have two lists as below:
-
6
SQL Group with Most Recent Record EachSay you have a table with student test data. SELECT t.* FROM tests t ORDER BY t.student_id, t.taken_on
-
7
SQL Query to Find all the Students with Marks Greater than Average MarksLast Updated : 19 Apr, 2021Query in SQL is like a statement that performs a task. Here, we need to...
-
12
Here's the average salary each generation says they need to feel 'financially healthy.' Gen Z requires a whopping $171K/year — but how do your own expectations compare?
-
7
How to Find Average Marks of Each Subject in SQL? ...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK