Here we will learn what is SQLite COUNT() function, how to use SQLite COUNT() function with Where, Group By, and DISTINCT clauses with examples.
In SQLite Count() Function is an aggregate function that is used to return the total number of rows in a table based on the specified expression or conditions.
Generally, in SQLite Count() function will work with non-NULL numeric values to return a number of times non-NULL values exist in a column based on the defined expression.
In SQLite Count() function, if we define the expression as an asterisk character (*) then the COUNT function will return the number of rows in a table based on the defined aggregate group or condition.
Following is the syntax of the SQLite count() function to return the total number of rows available with the defined expression.
In the above SQLite count() function syntax, we defined multiple parameters that are
Now we will see how to use the count() function in SQLite to get the total number of rows based on the value of an expression with examples for that we need to create two tables called dept_master and emp_master using the following query.
To create and insert some data in the dept_master table execute the following query.
Once we create and insert data execute the following query to see the data of the dept_master table.
dept_id dept_name
---------- ----------
1 Admin
2 Sales
3 Quality Control
4 Marketing
Same way execute the following queries to create and insert some data in the emp_master table.
Now run the following query to check the records of the emp_master table.
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
In SQLite the Count(*) function will return the total number of rows available in a table, including the rows which contain NULL values. The Count(*) will not take any parameters other than the asterisk symbol (*).
Now we will see how to use SQLite count(*) on emp_master to get the total number of records in the table.
count(*)
----------
9
If you observe the above example it returns the total number of rows available in the emp_master table. The above statement will return records even if NULL values exist in columns.
In SQLite, we use Group By clause with the Count() function to get the number of items that exist in a table based on the Group By expression.
Suppose I want to know how many employees exist in each department then By using the SQLite Group By clause with Count() function, we can quickly get that information.
Following is the SQLite query to use the Count() function with the Group By clause to get the number of employees that exists in each department.
count(*) dept_name
---------- ----------
2 Admin
4 Sales
3 Quality Control
If you observe the above example we got the employees count based on the department wise.
Generally in SQLite, we use the DISTINCT clause to get the UNIQUE number of records from a table, same way to get a Unique number of records from the table we use the DISTINCT with Count() function.
Following is the SQLite Count() function with DISTINCT to get each department's unique employee count.
dept_name count(emp_id)
---------- ----------
Admin 2
Sales 4
Quality Control 3
If you observe the above result we got the DISTINCT employees count based on the department id.
In SQLite, we can use the WHERE clause with the COUNT() function to return the number of rows from a table based on the defined conditions.
Suppose I want to get a number of employees from only the “Admin” department then by using the WHERE clause, we can easily get a count of employees only from the “Admin” department.
Following is the SQLite query to use the COUNT() function with WHERE clause to get employees count from only the “Admin” department.
dept_name count(*)
---------- ----------
Admin 2
If you observe the above example we are getting the count of employees whose department name equals to “Admin”.
This is how we can use the COUNT() function in SQLite to return a total number of rows based on our requirements.