SQLite Aggregate Functions: AVG

SQLite provides various aggregate functions that can be used to perform calculations on a set of values. One such function is the AVG() function, which returns the average of a set of values.

Syntax

The syntax of the AVG() function is as follows:

SELECT AVG(column_name) FROM table_name;

Example

Suppose we have a table named grades with columns subject and score, and we want to find the average score for a particular subject.

SELECT AVG(score) FROM grades WHERE subject = 'math';

Output

The output of the above SQL query would be the average score for the students who took the math test.

85

Explanation

The above example executes an SQL query that calculates the average of the score column for all the records in the grades table where the subject is equal to 'math'.

The AVG() function takes the column name as an argument and returns the average of the values in that column.

Use

The AVG() function can be used to calculate the average of a set of values in any data set or table. It is particularly useful when working with large amounts of data and needing to calculate the average without manually calculating it.

Important Points

• The AVG() function can only be used with numeric data types such as INTEGER, REAL, and FLOAT.
• If the column contains NULL values, the AVG() function will not consider these values in the calculation.
• If you want to find the average of all values in a column, simply omit the WHERE clause.
• You can combine AVG() with other aggregate functions like SUM and COUNT for more complex calculations.

Summary

In this tutorial, we learned about the AVG() aggregate function in SQLite, which is used to calculate the average of values in a column. We saw examples of how to use it to find the average of scores in a grades table. We also covered important points to keep in mind when using the AVG() function.