AVG function

SQLite AVG function is used to retrieve the average value of an expression.

Syntax:

  SELECT AVG(aggregate_expression)  

FROM tables  

[WHERE conditions];  

Syntax when you use AVG function with GROUP BY clause:

SELECT expression1, expression2, ... expression_n  

AVG(aggregate_expression)  

FROM tables  

[WHERE conditions]  

GROUP BY expression1, expression2, ... expression_n;  

Example1:

We have a table named “STUDENT”, having the following data:

Retrieve the average fees of all students in “STUDENT” table.

SELECT AVG(FEES) AS "Avg Fees"  

FROM STUDENT  

WHERE ID <= 5;  

Output:

Exampple2: Using DISTINCT clause with AVG function

Retrieve average distinct fees from “STUDENT” table where FEES is greater than 10000.

SELECT AVG(DISTINCT  FEES) AS "Avg Fees"  

FROM STUDENT  

WHERE FEES > 10000;  

Output:

Example3: Using mathematical formula with AVG function

You can use mathematical formulae to retrieve an average value according to your requirement:

SELECT AVG(FEES / 12) AS "Average Monthly Fees"  

FROM STUDENT;

Output:

Example4: Using GROUP BY clause with AVG Function

Retrieve NAME and FEES from the table and GROUP BY the result by AGE:

  SELECT NAME, SUM(FEES) AS "Avg Fees by Name"  

FROM STUDENT  

GROUP BY AGE;  

Output: