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:

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

Sample Output:

sqlite> SELECT cate_id,count(DISTINCT(pub_lang)), ROUND(AVG(no_page),2)
   ...> FROM book_mast
   ...> GROUP BY cate_id;
cate_id     count(DISTINCT(pub_lang))  ROUND(AVG(no_page),2)
----------  -------------------------  ---------------------
CA001       3                          264.33
CA002       1                          433.33
CA003       2                          256.67
CA004       3                          246.67
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

Sample Output:

sqlite> SELECT country,count(*)
   ...> FROM author
   ...> GROUP BY country;
country     count(*)
----------  ----------
Australia   2
Brazil      1
Canada      2
Germany     1
India       1
UK          4
USA         4

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

Sample Output:

sqlite> SELECT country,pub_city,count(*)
   ...> FROM publisher
   ...> GROUP BY country,pub_city;
country     pub_city    count(*)
----------  ----------  ----------
Australia   Adelaide    1
India       Mumbai      1
India       New Delhi   1
UK          Cambridge   1
UK          London      1
USA         Houstan     1
USA         New York    2

Previous: AVG()
Next: Group_Concat()