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:
Now, according to the equation of average, we can find the answer as:
average = 63.5
This calculation can be done by using the avg() function and the general syntax of avg() is:
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:
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:
We have another table of the weight of different cereals in grams, and to display the table we will use the command:
To find out the average of the weight of all these values of a column, “weight” is:
We can see the answer 64.306, to round off it on one digit after the decimal, use the following command:
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:
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.