What is aggregate average function in SQLite

SQLite is an open-source, RDMS(relational database management system) which is used to manage rows and columns of a table, in which data is stored, of a website or an application. SQLite has many built-in functions, which are used to perform a variety of tasks, one of them is a function of avg(), which is used to find out the average of provided data; this function is applicable to only INTEGER and REAL data types, where the average has a general formula:

Average = SUM OF Terms/NUMBER OF Terms

Now, in SQLite, to find the average by adding all the data whose average is to be found, and then dividing it by the number of data entries; we can directly find out the average of the data simply by the use of avg() function. In this write-up, we will explain how to use the avg() function in SQLite to find out the average of data.

How to use avg() function in SQLite

We have a table in the database, named students_result, containing the names of students, their unique student ids, and marks. To display this table, execute the following command in the terminal of Ubuntu:

SELECT * FROM students_result;

Now, according to the equation of average, we can find the answer as:

average = (80 + 65 + 98 + 33 + 43 + 78 + 93 + 56 + 77 + 12)/10
average = 63.5

This calculation can be done by using the avg() function and the general syntax of avg() is:

SELECT avg(column_name) FROM TABLE_NAME WHERE condition;

The explanation of the general syntax is:

Use SELECT clause to retrieve data from the table
Then write the avg and replace the column_name with the name of the column whose average is to be found
Use the FROM clause and replace the table_name, with the name of the table you want to access
Use WHERE clause if there is some condition to be applied

We will use the above table and find out the average using avg() and match its result with the above calculation:

SELECT avg(RESULT) FROM students_result;

From the output, we can see that the result is 63.5 which is the same as the result calculated by the equation. We can also find out the average of the first 5 rows of the result column by using:

SELECT avg(RESULT) FROM students_result WHERE result<6;

We have another table of the weight of different cereals in grams, and to display the table we will use the command:

SELECT * FROM cereals_weight;

To find out the average of the weight of all these values of a column, “weight” is:

SELECT avg(weight) FROM cereals_weight;

We can see the answer 64.306, to round off it on one digit after the decimal, use the following command:

SELECT round(avg(weight),1) FROM cereals_weight;

If we have some similar values, and we want to consider them only once while calculating the average, we can use the clause of DISTINCT in the avg() function. For example, we have a table of goals scored by different players and we want to find out the average only of those goals which are different like we have a list:

Players	Goals
Cristiano Ronaldo	5
Lionel Messi	5
Xavi	3
Andres Iniesta	1

In the above table, we wanted to calculate the average of only 5(we want to consider it for once),3, and 1, run the following statement:

SELECT avg(DISTINCT Goals) FROM Players_data;

We can see from the above output, it gave an average of 1,3,5 instead of 1,3,5,5.

Conclusion

Built-in functions not only save our time, but also provide efficient results, and in SQLite, there are many built-in functions, one of them is avg() function. In this writeup, we have discussed the avg() function which is used to find out the average of the data provided in the form of INTEGER and REAL data types, and along with avg(), round and DISTINCT clauses are used to round off the numbers and to find the average of values which are not same with each other respectively.

What is aggregate average function in SQLite

How to use avg() function in SQLite

Conclusion

About the author

Hammad Zahid