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.
Similar Reads
AVG() Function in MySQL
AVG() function : This function in MySQL is used to return the average value of the specified expression. Features : This function is used to find the average value of the specified expression.This function comes under Numeric Functions.This function accepts only one parameter namely expression.This
2 min read
SQL AVG() Function
The AVG() function in SQL is an aggregate function used to calculate the average (mean) value of a numeric column in a table. It provides insights into the central tendency of numerical data, making it an essential tool for statistical analysis and reporting. The function automatically excludes NULL
4 min read
PL/SQL AVG() Function
The PL/SQL AVG() function serves as a powerful tool for performing aggregate calculations on numeric datasets within a database. By allowing developers to calculate average values while excluding NULL entries, it enhances data analysis capabilities. In this article, we will explore the AVG() functio
5 min read
CHAR() Function in SQL
CHAR() : This function could be used to find the character based on the ASCII (American Standard Code for Information Interchange) code. CHAR() function do not support multiple integers as arguments. Syntax : SELECT CHAR(code); Note - Parameter code is mandatory, code evaluates to an integer value f
1 min read
SQL MIN() Function
The MIN() function in SQL is a powerful tool that allows us to determine the smallest or lowest value from a specified column or expression. It is widely used in data analysis to extract minimum values for decision-making, reporting, and business insights. This function automatically excludes NULL v
8 min read
DIV() Function in MySQL
DIV() function : This function in MySQL is used to return a quotient (integer) value when integer division is done. For example, when 7 is divided by 3, then 2 will be returned. Syntax : SELECT x DIV y; Parameter : This method accepts two parameters as given below as follows. x - Specified dividend
1 min read
DAY() Function in MySQL
DAY() function : This function in MySQL is used to return the day of the month for a specified date (a number from 1 to 31). This function equals the DAYOFMONTH() function. Syntax : DAY(date) Parameter : This method accepts a parameter which is illustrated below : date : Specified date to extract th
1 min read
AVG() Function in SQL Server
The AVG() function in SQL Server is an essential aggregate function used to compute the average value of a numeric column. It works by summing all non-NULL values in the specified column and then dividing the total by the number of these values. In this article, We will learn about AVG() Function in
4 min read
ELT() Function in MySQL
In this article, we are going to cover ELT function with examples. In ELT function, number field will state that how many strings will be there. ELT function in MySQL is used to returns the string which is at index number specified in the argument list. In this function there is number field and str
1 min read
PL/SQL MIN() Function
PL/SQL means Procedural Language / relational Structured Query Language, the extended language of Oracle. It is primarily used to manage and manipulate databases. One of the most frequently utilized SQL functions is the MIN() function. This powerful aggregate function is essential for finding the sm
6 min read