Set Up an Environment
CREATE TABLE student_scores (
student_id INT,
subject VARCHAR(50),
score INT
);
INSERT INTO student_scores (student_id, subject, score) VALUES
(1, 'Math', 85),
(2, 'Science', 78),
(3, 'English', 92),
(4, 'Math', 90),
(5, 'Science', 82),
(6, 'English', 88),
(7, 'Math', 75),
(8, 'Science', 80),
(9, 'English', 85);
Output:
| student_id | subject | score |
|------------|------------|-------|
| 1 | Math | 85 |
| 2 | Science | 78 |
| 3 | English | 92 |
| 4 | Math | 90 |
| 5 | Science | 82 |
| 6 | English | 88 |
| 7 | Math | 75 |
| 8 | Science | 80 |
| 9 | English | 85 |
Example 1: Calculating Average Score per Subject
SELECT subject, AVG(score) AS average_score
FROM student_scores
GROUP BY subject;
Output:
| subject | average_score |
|------------|---------------|
| Math | 83.3333 |
| Science | 80 |
| English | 88.3333 |
Explanation: In this example, we’re using the AVG() function to compute the average score for each subject. The GROUP BY clause is used to group the results by the subject column. The output will display two columns: subject and average_score.
Example 2: Calculating Overall Average Score
SELECT AVG(score) AS overall_average_score
FROM student_scores;
Output:
| overall_average_score |
|-----------------------|
| 83.88888888888889 |
Explanation: Here, we’re computing the average score across all subjects using the AVG() function without any grouping. This will give us a single value representing the overall average score of all students
Example 3: Calculating Average Score for a Specific Subject
SELECT AVG(score) AS average_science_score
FROM student_scores
WHERE subject = 'Science';
Output:
| average_science_score |
|-----------------------|
| 80 |
Explanation: In this example, we’re filtering the data using the WHERE clause to focus only on the “Science” subject. Then, we use the AVG() function to calculate the average score for that specific subject
AVG() Function in SQL
SQL is an RDBMS system in which SQL functions become very essential to provide us with primary data insights. One of the most important functions is called AVG() and is particularly useful for the calculation of averages within datasets.
In this, we will learn about the AVG() function, and its syntax by understanding various examples and their output with explanation and so on.