SQLite count function

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

SQLite count() function

Introduction

count (x)

The count(X) function returns a count of the number of times that X is not NULL in a group.

Syntax :

  1. count(column)   

Returns the number of times that a non-NULL value appears in column

count (*)

The count(*) function (with no arguments) returns the total number of rows in the group.

Syntax :

count(*)

Returns the total number of rows in a query, regardless of NULL values

Example : SQLite count(DISTINCT) function

The following SQLite statement will count the unique 'pub_lang' and average of 'no_page' up to 2 decimal places for each group of 'cate_id'.

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 cate_id,count(DISTINCT(pub_lang)), ROUND(AVG(no_page),2)  
  2.    ...> FROM book_mast  
  3.    ...> GROUP BY cate_id;  
  4. cate_id     count(DISTINCT(pub_lang))  ROUND(AVG(no_page),2)  
  5. ----------  -------------------------  ---------------------  
  6. CA001       3                          264.33  
  7. CA002       1                          433.33  
  8. CA003       2                          256.67  
  9. CA004       3                          246.67  
  10. CA005       4                          245.75  

Example : SQLite count() function with GROUP BY

The following SQLite statement will show number of author for each country. The GROUP BY clause groups all records for each country and then count() function in conjunction with GROUP BY counts the number of authors for each country.

Sample table : author

aut_id aut_name country home_city
AUT001 William Norton UK Cambridge
AUT002 William Maugham Canada Toronto
AUT003 William Anthony UK Leeds
AUT004 S.B.Swaminathan India Bangalore
AUT005 Thomas Morgan Germany Arnsberg
AUT006 Thomas Merton USA New York
AUT007 Piers Gibson UK London
AUT008 Nikolai Dewey USA Atlanta
AUT009 Marquis de Ellis Brazil Rio De Janerio
AUT010 Joseph Milton USA Houston
AUT011 John Betjeman Hunter Australia Sydney
AUT012 Evan Hayek Canada Vancouver
AUT013 E. Howard Australia Adelaide
AUT014 C. J. Wilde UK London
AUT015 Butler Andre USA Florida
  1. sqlite> SELECT country,count(*)  
  2.    ...> FROM author  
  3.    ...> GROUP BY country;  
  4. country     count(*)  
  5. ----------  ----------  
  6. Australia   2  
  7. Brazil      1  
  8. Canada      2  
  9. Germany     1  
  10. India       1  
  11. UK          4  
  12. USA         4  

Pictorial Presentation

mysql count with group by example2 pictorial presentation

SQLite count() function with group by on multiple columns

The following SQLite statement returns a number of publishers in each city for a country. The grouping operation is performed on country and pub_city column with the use of GROUP BY and then count() counts the number of publishers for each group.

Sample table : publisher

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 country,pub_city,count(*)  
  2.    ...> FROM publisher  
  3.    ...> GROUP BY country,pub_city;  
  4. country     pub_city    count(*)  
  5. ----------  ----------  ----------  
  6. Australia   Adelaide    1  
  7. India       Mumbai      1  
  8. India       New Delhi   1  
  9. UK          Cambridge   1  
  10. UK          London      1  
  11. USA         Houstan     1  
  12. USA         New York    2