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.

AVG() Function in SQL

The SQL function AVG() is designed for calculating the average value of any numeric column within a certain data set. It does this by adding up all the values of the column and then dividing the resulting number by the number of non-null values of the column.

Thus, the function is best suited to propose a typical value of a given data set which helps to analyze the data-set features.

Syntax:

The syntax of the AVG() function is straightforward:

SELECT AVG(column_name)

FROM table_name;

Here, column_name represents the column from which you want to compute the average, and table_name is the name of the table containing the data. Optionally, you can use the WHERE clause to specify conditions for filtering the data before calculating the average.

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

Considerations

While the AVG() function is a powerful tool for data analysis, there are some considerations to keep in mind:

• Handling NULL Values: AVG () function by default ignore NULL values when calculating the total. However, they have to be careful in how they interpret the results of the study, especially when missing values are relevant to your study.
  
• Data Type Compatibility: Make sure the function of AVG() is applied to the column that contains numbers as the values. The usage of it beyond numerical columns will result in erroneous results hence.
  
• Precision and Rounding: Since rounding is used in different cases, you can either keep the result of the AVG() function as it is, or you may round it to a specific number of decimal places so that the result is clear and consistent

Conclusion

In SQL, the AVG() function is a good function for studying numerical data. It enables analysts as well as data experts to carry out easy calculations and thereby give a clear understanding regarding various vital factors in their datasets. Whether you are deciding on sales figures, evaluating student performance or monitoring website statistics AVG() gives you the statistics with facts on which you can depend when making important decisions.