Question: The GROUP BY Clause and the AVG Function
Explore how to write SQL queries that use the GROUP BY clause along with the AVG function to calculate average marks per subject from student grade data. Understand how to aggregate and group results to analyze academic performance efficiently.
We'll cover the following...
Question
Given the following StudentGrades table structure:
StudentGrades
RecordID | StudentID | StudentName | Subject | Marks |
1 | st-101 | Alexa | Mathematics | 90.00 |
2 | st-101 | Alexa | Science | NULL |
3 | st-102 | Diana | Mathematics | 80.00 |
4 | st-102 | Diana | Science | 88.00 |
5 | st-103 | Carl | Mathematics | NULL |
6 | st-103 | Carl | Science | 92.00 |
7 | st-104 | Sara | Mathematics | 75.00 |
8 | st-104 | Sara | Science | 85.00 |
9 | st-105 | Karen | Mathematics | 85.00 |
10 | st-105 | Karen | Science | 90.00 |
Additional information
You are provided with a table named StudentGrades that contains the following columns:
RecordID: A column of unique values. It’s the primary key of this table.StudentID: Unique identifier for each student.StudentName: The name of the student.Subject: The subject for which the grade is recorded.Marks: The marks obtained by the student in the corresponding subject.
You are required to analyze student performance data. The StudentGrades table holds important details like IDs and names along with the marks of the students obtained in Science and Mathematics. Here’s a task for you: To do this, write a query using GROUP BY in SQL that calculates the average of marks for each subject.
This query will help us understand the overall performance across different subjects, providing valuable insights into academic achievements. Let’s try to solve it!
Expected output
The expected output is shown below:
Subject | AVG(Marks) |
Mathematics | 82.500000 |
Science | 88.750000 |
Try it yourself
You can try to write a query in the following playground:
Hints
Below are some hints to help you understand these concepts better: