SQLite AVG function

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

Syntax:

snippet
SELECT AVG(aggregate_expression)
FROM tables
[WHERE conditions];

Syntax when you use AVG function with GROUP BY clause:

snippet
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:

Sqlite Avg function 1

Retrieve the average fees of all students in "STUDENT" table.

snippet
SELECT AVG(FEES) AS "Avg Fees"
FROM STUDENT
WHERE ID <= 5;

Output:

Sqlite Avg function 2

Exampple2: Using DISTINCT clause with AVG function

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

snippet
SELECT AVG(DISTINCT  FEES) AS "Avg Fees"
FROM STUDENT
WHERE FEES > 10000;

Output:

Sqlite Avg function 3

Example3: Using mathematical formula with AVG function

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

snippet
SELECT AVG(FEES / 12) AS "Average Monthly Fees"
FROM STUDENT;

Output:

Sqlite Avg function 4

Example4: Using GROUP BY clause with AVG Function

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

snippet
SELECT NAME, SUM(FEES) AS "Avg Fees by Name"
FROM STUDENT
GROUP BY AGE;

Output:

Sqlite Avg function 5
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents