SQLite AVG() Function

  Total Views : 382
  Comments : 0
  Keywords : sqlite average function with example, sqlite get avg() function to calculate average count with example, sqlite avg() aggregate function with example

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 which is used to calculate the average value of given expression.

 

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

 

In case if we apply avg() function with non numeric 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.

 

Select avg(DISTINCT ALL Expressionfrom tablename

[WHERE condition]

[GROUP BY Expression];

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 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 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.

 

CREATE TABLE dept_master

(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,

dept_name TEXT);

 

INSERT INTO dept_master(dept_name)

VALUES('Admin'),

('Sales'),

('Quality Control'),

('Marketing');

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

 

sqliteSELECT FROM dep_master;

 

dept_id     dept_name

----------  ----------

1           Admin

2           Sales

3           Quality Control

4           Marketing

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

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);

 

INSERT INTO emp_master

values (1,'Honey','Patel', 10100,1),

(2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),

(5,'Shweta','Rana',12000,3),

(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 50000,3),

(9,'Shwets','Jariwala',19400,2);

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

 

sqliteSELECT FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

1           Honey       Patel       10100       1

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

9           Shwets      Jariwala    19400       2

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

 

sqliteSELECT avg(salaryFROM emp_master;

 

avg(salary)

-----------

19822.22222

 If you observe above example we are calculating the average salary of employees from 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 deparatement. 

 

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

 

sqliteSELECT avg(e.salary), d.dept_name 

FROM emp_master e, dept_master d 

WHERE e.dept_id=d.dept_id 

Group By e.dept_id;

 

avg(e.salary)  dept_name

-------------  ----------

11550.0        Admin

14550.0        Sales

32366.6666666 Quality Control

If you observe result we got the average salary of each deparatement 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 deparatements. Suppose if I want to calculate the average salary of 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 “Admin” department.

 

sqliteSELECT avg(e.salary),d.dept_name 

FROM emp_master e, dept_master d 

WHERE e.dept_id=d.dept_id 

GROUP BY e.dept_id 

HAVING d.dept_name 'Admin';

 

avg(e.salary)  dept_name

-------------  ----------

11550.0        Admin

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

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 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 ROUND function.

 

sqliteSELECT ROUND(avg(e.salary),2),d.dept_name 

FROM emp_master e, dept_master d 

WHERE e.dept_id=d.dept_id 

GROUP BY e.dept_id 

HAVING d.dept_name LIKE 'Quali%';

 

ROUND(avg(e.salary),2)  dept_name

----------------------  ---------------

32366.67               Quality Control

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 particular department using avg() function along with DISTINCT clause.

 

sqliteSELECT avg(DISTINCT(e.salary)),d.dept_name 

FROM emp_master e, dept_master d 

WHERE e.dept_id=d.dept_id 

GROUP BY e.dept_id 

HAVING d.dept_name LIKE 'Quali%';

 

ROUND(avg(e.salary),2)  dept_name

----------------------  ---------------

32366.67               Quality Control

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 SQLite avg() function to calculate the average values based on our requirements. 

 

PREVIOUS

SQLite Vacuum
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User