Aggregate Functions avg

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

SQLite avg() function

Description

SQLite avg() function retrieves the average value of a given expression. If the function does not find a matching row, it returns NULL. String and BLOB values that do not look like numbers are interpreted as 0. The result of avg() is always a floating point value as long as at there is at least one non-NULL input even if all inputs are integers. The result of avg() is NULL if and only if there are no non-NULL inputs.

Syntax

avg([DISTINCT] expr)

Where expr is a given expression. The DISTINCT option can be used to return the average of the distinct values of expr.

Table of Contents

Example : SQLite avg() function

SQLite avg() function with group by and example

SQLite avg() function with distinct and example

SQLite avg() function decimal places and example

SQLite avg() function with count() function and example

SQLite avg() function with having and example

Example : SQLite avg() function

The following SQLite statement will return an average number of pages (of books) from the book_mast table.

Sample table : book_mast

book_id book_name isbn_no cate_id aut_id pub_id dt_of_pub pub_lang no_page book_price
BK001 Introduction to Electrodynamics 0000979001 CA001 AUT001 P003 2001-05-08 English 201 85.00
BK002 Understanding of Steel Construction 0000979002 CA002 AUT002 P001 2003-07-15 English 300 105.50
BK003 Guide to Networking 0000979003 CA003 AUT003 P002 2002-09-10 Hindi 510 200.00
BK004 Transfer of Heat and Mass 0000979004 CA002 AUT004 P004 2004-02-16 English 600 250.00
BK005 Conceptual Physics 0000979005 CA001 AUT005 P006 2003-07-16   345 145.00
BK006 Fundamentals of Heat 0000979006 CA001 AUT006 P005 2003-08-10 German 247 112.00
BK007 Advanced 3d Graphics 0000979007 CA003 AUT007 P002 2004-02-16 Hindi 165 56.00
BK008 Human Anatomy 0000979008 CA005 AUT008 P006 2001-05-17 German 88 50.50
BK009 Mental Health Nursing 0000979009 CA005 AUT009 P007 2004-02-10 English 350 145.00
BK010 Fundamentals of Thermodynamics 0000979010 CA002 AUT010 P007 2002-10-14 English 400 225.00
BK011 The Experimental Analysis of Cat 0000979011 CA004 AUT011 P005 2007-06-09 French 225 95.00
BK012 The Nature of World 0000979012 CA004 AUT005 P008 2005-12-20 English 350 88.00
BK013 Environment a Sustainable Future 0000979013 CA004 AUT012 P001 2003-10-27 German 165 100.00
BK014 Concepts in Health 0000979014 CA005 AUT013 P004 2001-08-25   320 180.00
BK015 Anatomy & Physiology 0000979015 CA005 AUT014 P008 2000-10-10 Hindi 225 135.00
BK016 Networks and Telecommunications 00009790_16 CA003 AUT015 P003 2002-01-01 French 95 45.00
  1. sqlite> SELECT avg(no_page) FROM book_mast;  
  2. 286.625  

Example : SQLite avg() function with group by

SQLite avg() function retrieves the average value of a given expression for each group, if it is used with group by option. The following statement will return the average number of pages for each group of 'pub_id' from book_mast table

book_id book_name isbn_no cate_id aut_id pub_id dt_of_pub pub_lang no_page book_price
BK001 Introduction to Electrodynamics 0000979001 CA001 AUT001 P003 2001-05-08 English 201 85.00
BK002 Understanding of Steel Construction 0000979002 CA002 AUT002 P001 2003-07-15 English 300 105.50
BK003 Guide to Networking 0000979003 CA003 AUT003 P002 2002-09-10 Hindi 510 200.00
BK004 Transfer of Heat and Mass 0000979004 CA002 AUT004 P004 2004-02-16 English 600 250.00
BK005 Conceptual Physics 0000979005 CA001 AUT005 P006 2003-07-16   345 145.00
BK006 Fundamentals of Heat 0000979006 CA001 AUT006 P005 2003-08-10 German 247 112.00
BK007 Advanced 3d Graphics 0000979007 CA003 AUT007 P002 2004-02-16 Hindi 165 56.00
BK008 Human Anatomy 0000979008 CA005 AUT008 P006 2001-05-17 German 88 50.50
BK009 Mental Health Nursing 0000979009 CA005 AUT009 P007 2004-02-10 English 350 145.00
BK010 Fundamentals of Thermodynamics 0000979010 CA002 AUT010 P007 2002-10-14 English 400 225.00
BK011 The Experimental Analysis of Cat 0000979011 CA004 AUT011 P005 2007-06-09 French 225 95.00
BK012 The Nature of World 0000979012 CA004 AUT005 P008 2005-12-20 English 350 88.00
BK013 Environment a Sustainable Future 0000979013 CA004 AUT012 P001 2003-10-27 German 165 100.00
BK014 Concepts in Health 0000979014 CA005 AUT013 P004 2001-08-25   320 180.00
BK015 Anatomy & Physiology 0000979015 CA005 AUT014 P008 2000-10-10 Hindi 225 135.00
BK016 Networks and Telecommunications 00009790_16 CA003 AUT015 P003 2002-01-01 French 95 45.00
  1. sqlite> SELECT pub_id,avg(no_page) FROM book_mast GROUP BY pub_id;  
  2. pub_id      avg(no_page)  
  3. ----------  ------------  
  4. P001        232.5  
  5. P002        337.5  
  6. P003        148.0  
  7. P004        460.0  
  8. P005        236.0  
  9. P006        216.5  
  10. P007        375.0  
  11. P008        287.5 

Pictorial Presentation

sqlite average with group by example1 pictorial presentation

Example : SQLite avg() function with distinct

SQLite avg() function retrieves the unique average value of a given expression when used with DISTINCT keyword. The following statement will return the average of unique 'receive_qty' from the purchase table.

Sample table : purchase

pub_id pub_name pub_city country country_office no_of_branch estd
P001 Jex Max Publication New York USA New York 15 1969-12-25
P002 BPP Publication Mumbai India New Delhi 10 1985-10-01
P003 New Harrold Publication Adelaide Australia Sydney 6 1975-09-05
P004 Ultra Press Inc. London UK London 8 1948-07-10
P005 Mountain Publication Houstan USA Sun Diego 25 1975-01-01
P006 Summer Night Publication New York USA Atlanta 10 1990-12-10
P007 Pieterson Grp. of Publishers Cambridge UK London 6 1950-07-15
P008 Novel Publisher Ltd. New Delhi India Bangalore 10 2000-01-01
  1. sqlite> SELECT avg(DISTINCT(receive_qty))  
  2.    ...> FROM purchase;  
  3. avg(DISTINCT(receive_qty))  
  4. --------------------------  
  5. 14.3333333333333  
  6.     

Pictorial Presentation

sqlite average with group by example2 pictorial presentation

 Example : SQLite avg() function decimal places

Here we have discussed how to use ROUND() along with avg() to retrieve a value calculated upto a specific number of decimal places of a given value. The following statement will return the average number of pages up to 2 decimal places for each group of 'pub_id' from book_mast table.

Sample table : book_mast

book_id book_name isbn_no cate_id aut_id pub_id dt_of_pub pub_lang no_page book_price
BK001 Introduction to Electrodynamics 0000979001 CA001 AUT001 P003 2001-05-08 English 201 85.00
BK002 Understanding of Steel Construction 0000979002 CA002 AUT002 P001 2003-07-15 English 300 105.50
BK003 Guide to Networking 0000979003 CA003 AUT003 P002 2002-09-10 Hindi 510 200.00
BK004 Transfer of Heat and Mass 0000979004 CA002 AUT004 P004 2004-02-16 English 600 250.00
BK005 Conceptual Physics 0000979005 CA001 AUT005 P006 2003-07-16   345 145.00
BK006 Fundamentals of Heat 0000979006 CA001 AUT006 P005 2003-08-10 German 247 112.00
BK007 Advanced 3d Graphics 0000979007 CA003 AUT007 P002 2004-02-16 Hindi 165 56.00
BK008 Human Anatomy 0000979008 CA005 AUT008 P006 2001-05-17 German 88 50.50
BK009 Mental Health Nursing 0000979009 CA005 AUT009 P007 2004-02-10 English 350 145.00
BK010 Fundamentals of Thermodynamics 0000979010 CA002 AUT010 P007 2002-10-14 English 400 225.00
BK011 The Experimental Analysis of Cat 0000979011 CA004 AUT011 P005 2007-06-09 French 225 95.00
BK012 The Nature of World 0000979012 CA004 AUT005 P008 2005-12-20 English 350 88.00
BK013 Environment a Sustainable Future 0000979013 CA004 AUT012 P001 2003-10-27 German 165 100.00
BK014 Concepts in Health 0000979014 CA005 AUT013 P004 2001-08-25   320 180.00
BK015 Anatomy & Physiology 0000979015 CA005 AUT014 P008 2000-10-10 Hindi 225 135.00
BK016 Networks and Telecommunications 00009790_16 CA003 AUT015 P003 2002-01-01 French 95 45.00
  1. sqlite> SELECT pub_id,ROUND(avg(no_page),2)  
  2.    ...> FROM book_mast  
  3.    ...> GROUP BY pub_id;  
  4. pub_id      ROUND(avg(no_page),2)  
  5. ----------  ---------------------  
  6. P001        232.5  
  7. P002        337.5  
  8. P003        148.0  
  9. P004        460.0  
  10. P005        236.0  
  11. P006        216.5  
  12. P007        375.0  
  13. P008        287.5  
  14.   

Example : SQLite avg() function with count() function

Here we have discussed how to use SQLite avg() function with count() function to fetch suitable data. The following statement will return the average 'no_page' and number of the publisher for each group of the publisher from book_mast table.

book_id book_name isbn_no cate_id aut_id pub_id dt_of_pub pub_lang no_page book_price
BK001 Introduction to Electrodynamics 0000979001 CA001 AUT001 P003 2001-05-08 English 201 85.00
BK002 Understanding of Steel Construction 0000979002 CA002 AUT002 P001 2003-07-15 English 300 105.50
BK003 Guide to Networking 0000979003 CA003 AUT003 P002 2002-09-10 Hindi 510 200.00
BK004 Transfer of Heat and Mass 0000979004 CA002 AUT004 P004 2004-02-16 English 600 250.00
BK005 Conceptual Physics 0000979005 CA001 AUT005 P006 2003-07-16   345 145.00
BK006 Fundamentals of Heat 0000979006 CA001 AUT006 P005 2003-08-10 German 247 112.00
BK007 Advanced 3d Graphics 0000979007 CA003 AUT007 P002 2004-02-16 Hindi 165 56.00
BK008 Human Anatomy 0000979008 CA005 AUT008 P006 2001-05-17 German 88 50.50
BK009 Mental Health Nursing 0000979009 CA005 AUT009 P007 2004-02-10 English 350 145.00
BK010 Fundamentals of Thermodynamics 0000979010 CA002 AUT010 P007 2002-10-14 English 400 225.00
BK011 The Experimental Analysis of Cat 0000979011 CA004 AUT011 P005 2007-06-09 French 225 95.00
BK012 The Nature of World 0000979012 CA004 AUT005 P008 2005-12-20 English 350 88.00
BK013 Environment a Sustainable Future 0000979013 CA004 AUT012 P001 2003-10-27 German 165 100.00
BK014 Concepts in Health 0000979014 CA005 AUT013 P004 2001-08-25   320 180.00
BK015 Anatomy & Physiology 0000979015 CA005 AUT014 P008 2000-10-10 Hindi 225 135.00
BK016 Networks and Telecommunications 00009790_16 CA003 AUT015 P003 2002-01-01 French 95 45.00

  1. sqlite> SELECT pub_id, avg(no_page)  
  2.    ...> FROM book_mast  
  3.    ...> GROUP BY pub_id  
  4.    ...> HAVING  pub_id='P008';  
  5. pub_id      avg(no_page)  
  6. ----------  ------------  
  7. P008        287.5  

SQLite avg() function with having

SQLite avg() function retrieves the average value of a given expression against a condition specified after HAVING clause for each group specified after the GROUP BY clause. This way you can use HAVING and GROUP BY with SQLite avg() function. The following statement will return the average no_page for those group whose 'pub_id' is 'P008' from the book_mast table.

Sample table : book_mast

book_id book_name isbn_no cate_id aut_id pub_id dt_of_pub pub_lang no_page book_price
BK001 Introduction to Electrodynamics 0000979001 CA001 AUT001 P003 2001-05-08 English 201 85.00
BK002 Understanding of Steel Construction 0000979002 CA002 AUT002 P001 2003-07-15 English 300 105.50
BK003 Guide to Networking 0000979003 CA003 AUT003 P002 2002-09-10 Hindi 510 200.00
BK004 Transfer of Heat and Mass 0000979004 CA002 AUT004 P004 2004-02-16 English 600 250.00
BK005 Conceptual Physics 0000979005 CA001 AUT005 P006 2003-07-16   345 145.00
BK006 Fundamentals of Heat 0000979006 CA001 AUT006 P005 2003-08-10 German 247 112.00
BK007 Advanced 3d Graphics 0000979007 CA003 AUT007 P002 2004-02-16 Hindi 165 56.00
BK008 Human Anatomy 0000979008 CA005 AUT008 P006 2001-05-17 German 88 50.50
BK009 Mental Health Nursing 0000979009 CA005 AUT009 P007 2004-02-10 English 350 145.00
BK010 Fundamentals of Thermodynamics 0000979010 CA002 AUT010 P007 2002-10-14 English 400 225.00
BK011 The Experimental Analysis of Cat 0000979011 CA004 AUT011 P005 2007-06-09 French 225 95.00
BK012 The Nature of World 0000979012 CA004 AUT005 P008 2005-12-20 English 350 88.00
BK013 Environment a Sustainable Future 0000979013 CA004 AUT012 P001 2003-10-27 German 165 100.00
BK014 Concepts in Health 0000979014 CA005 AUT013 P004 2001-08-25   320 180.00
BK015 Anatomy & Physiology 0000979015 CA005 AUT014 P008 2000-10-10 Hindi 225 135.00
BK016 Networks and Telecommunications 00009790_16 CA003 AUT015 P003 2002-01-01 French 95 45.00
  1. sqlite> SELECT pub_id, avg(no_page)  
  2.    ...> FROM book_mast  
  3.    ...> GROUP BY pub_id  
  4.    ...> HAVING  pub_id='P008';  
  5. pub_id      avg(no_page)  
  6. ----------  ------------  
  7. P008        287.5