SQLite AVG() Function

Here we will learn what is SQLite average or avg() function and how to use sqlite avg() function with Group By, Having, DISTINCT to calculate average values with syntax and examples

SQLite AVG() Function

In SQLite average or avg() function is an aggregate function that is used to calculate the average value of the given expression.

 

Generally, in SQLite avg() function will work with non-NULL numeric values to aggregate and return average values based on a defined expression. The SQLite avg() function will always return the result in float format. 

 

In case if we apply avg() function with nonnumeric values like text or BLOB it will consider as 0 and if all the numbers are NULL means ag() function will return a NULL.

Syntax of SQLite Avg() Function

Following is the syntax of SQLite avg() function to calculate the average of non-null values.

 

In above SQLite avg() function syntax we defined multiple parameters those are

 

DISTINCT | ALL – By default SQLite avg() function uses ALL to calculate average value of given expression so ALL is an Optional we don’t need to specify it separately. In case if we need to calculate average on DISTINCT values then we need to specify it with expression.

 

Expression – Its column or expression which we need to calculate the average value.

 

Tablename – Its name of the table which we used to calculate average values.

 

[WHERE Condition] – If we have any specific conditions to calculate average value then we will specify it based on our requirements. It’s optional.

 

[Group By Expression] – If we need to calculate average specifically based on Expression value we need to use Group By with expression. It’s an optional.

Example of SQLite Avg() Function

Now we will see how to use avg() function in SQLite to calculate the average value of an expression with examples for that we need to create two tables called dept_master and emp_master using following query.

 

To create and insert some data in dept_master table execute following query.

 

Once we create and insert data execute the following query to see the data of the dept_master table.

 

 Same way execute the following queries to create and insert some data in the emp_master table.

 

Now run the following query to check records of emp_master table.

 

Now write the query like as shown following to get an average salary from the emp_master table.

 

 If you observe the above example we are calculating the average salary of employees from the emp_master table.

 

As we discussed avg() function always return result in float values format.

SQLite Avg() Function with Group By Clause

Generally in SQLite, if we want to retrieve the data based on specific group-wise then we will use Group By clause with our Select statements. 

 

Now we will see how to use SQLite avg() function with Group By clause to get the average salary for each department. 

 

Following is the query to use SQLite avg() function with Group By clause to get an average salary of each department.

 

If you observe result we got the average salary of each department by using SQLite avg() function with Group By clause.

SQLite Avg() Function with Having Clause

By using SQLite avg() function with Group By clause we calculated average salary of all the departments. Suppose if I want to calculate the average salary of a particular department like only “Admin” then in that case we need to use Having with Group By clause along with SQLite avg() function.

 

Following is the query to use SQLite Avg() function with Having clause to get the average salary of the “Admin” department.

 

 If you observe above result we got the average salary of only the “Admin” department.

SQLite Avg() Function with Decimal Values

If you observe above SQLite avg() function examples we are getting average values with more number of decimal values. In SQLite, by using the ROUND function we can restrict that decimal values based on our requirements.

 

Following is the SQLite avg() function query to restrict the decimal values of average calculation by using the ROUND function.

 

If you observe above result we restricted the above average value to 2 decimal places using ROUND() function otherwise it will return value like “182366.666666667”.

SQLite Avg() Function with DISTINCT

Suppose in SQLite if we want to calculate the average for DISTINCT values then by using DISTINCT clause with avg() function we can easily calculate average for UNIQUE values.

 

Following is the SQLite query to calculate the average salary of a particular department using avg() function along with the DISTINCT clause.

 

If you observe above query we are calculating the average salary of “Quality Control” department by taking only DISTINCT or UNIQUE values from salary.

 

This is how we can use the SQLite avg() function to calculate the average values based on our requirements.