SQLite AVG function is used to retrieve the average value of an expression.
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;
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;
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;
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;
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;