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

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.