SQLite Tutorial SQLite Advanced SQLite Database SQLite References

SQLite COUNT() Function



The SQLite COUNT() function returns the count of an expression where the value of expression is not NULL.

Note: The COUNT() function with no arguments returns the total number of rows in the group.

Syntax

The syntax for using COUNT() function is given below:

SELECT COUNT(column_name) 
FROM table_name
WHERE condition(s);

The SQLite GROUP BY clause is used to arrange result table into identical groups when one or more columns are used. Please note that it is must to include those column names in a GROUP BY clause which are not encapsulated within the COUNT() function. See the syntax below:

SELECT column1, column2, ...
       COUNT(column_name) 
FROM table_name
WHERE condition(s)
GROUP BY column1, column2, ...;

Parameters

column1, column2, ... Specify the column names that are not encapsulated within the COUNT() function. It must be included in the GROUP BY clause.
column_name Specify the column or expression whose non-null values need to be counted.
table_name Specify the table name from where the records need to retrieved.
WHERE condition(s) Optional. Specify the condition(s). Records are selected based upon specified condition(s).

Return Value

Returns the count of non-null value of a given expression.

Example - With Single Column

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

To get the count of employees whose age is greater than 25, the following query can be used:

SELECT COUNT(Name) AS EmployeeGT25 
FROM Employee
WHERE Age > 25;

This will produce the result as shown below:

EmployeeGT25
4


Example - Using DISTINCT

The DISTICT clause can be used with COUNT() function. For example - To get the count of distinct (unique) age of the employees, the following code can be used:

SELECT COUNT(DISTINCT Age) AS DistinctAge 
FROM Employee;

This will produce the result as shown below:

DistinctAge
5


Example - Using GROUP BY

To get the count of employees group by their age, the following query can be used:

SELECT Age, COUNT(Age) AS NumberOfEmployee 
FROM Employee
GROUP BY Age;

This result of the above code will be:

AgeNumberOfEmployee
241
251
271
282
301

❮ SQLite Functions