Updated April 3, 2023
Definition of SQLite COUNT Function
SQLite provides the various kinds of aggregation functions to the user and one of those aggregation functions is provided by the SQLite database. The SQLite count () function is an aggregate function that is used to fetch the total number of rows from the table by using the specified expression and conditions. Basically, the SQLite count () function has a capacity to work with nonnull numeric values, it is used to fetch the total of how many times column exits the nonnull values in the column and that is based on the specified expression. In SQLite count () function, if specified expression contains the asterisk character (*) at that time count () function fetch the number rows dependent on specified aggregate group or condition.
Syntax:
select count (DISTINCT or all specified expression) from specified table name [WHERE specified condition] [GROUP BY specified expression];Explanation:
In the above SQLite count () function syntax uses different parameters as follows.
DISTINCT | ALL: In SQLite count () function by default uses ALL parameter and it is used to count how many rows are present in a table, based on the specified expression, that means ALL is an optional part of this syntax so there is no need to separately specify it in expression.
Specified Expression: Specified expression basically is nothing but the column or we can say expression and that can help us to determine how many non-null rows or values present in that table.
WHERE specified condition: Suppose we need to count how many rows are available in a specified table at that time we need to use the WHERE clause as per the requirement and it is an optional part of the syntax.
GROUP BY specified expression: Sometimes we need to find out how many rows are present in a specified table and that based on the expression at that we need to specify the GROUP BY clause and it is an optional part of the syntax.
Specified table name: It is an actual table that we need to fetch the records from the specified table.
How count function works in SQLite?
Now let’s see how the count () function works in SQLite as follows.
Basically, the SQLite count () function is an aggregate function; it works with different arguments such as WHERE, DISTINCT, ALL, and GROUP BY clauses.
When we specify the ALL argument in the SQL statement that we return all non-null values including the duplicates values and it by default parameter of SQLite if we need skip then we can easily skip.
When we use DISTINCT in the SQL statement then it returns the only unique values with non-null values. The working SQLite count () function is simple and we use it as per the requirement with different parameters or we can say that argument.
Examples
Now let’s see the different examples of SQLite count () function as follows. First, we need to create a table by using the following statement as follows.
create table emp (emp_id integer primary key. emp_first_name text, emp_last_name text, emp_salary numeric, emp_dept_id integer);Explanation
In the above example, we use create table statement to create a new table name as emp with different attributes with different data types such as emp_id is an integer data type with primary, emp_first_name, emp_last_name, emp_salary, and emp_dept_id as shown in the above statement. The end out of the above statement we illustrated by using the following screenshot.
Now insert some records by using the following statement as follows.
insert into emp(emp_id, emp_first_name, emp_last_name, emp_salary, emp_dept_id) values (1, "Sunny","Patel", 10000, 5),
(2, "Jenny", "Sharma", 20000, 2),
(3, "Johan", "Gupta", 25000, 2),
(4, "Vinay", "Rana", 8500, 4);Explanation
In the above example, we use to insert into a statement to insert new records into the emp table with different values as shown in the above statement. The end out of the above statement we illustrated by using the following screenshot.
select * from emp;
Similarly, we created another table name as emp_dept as shown in the following screenshot as follows.
select * from emp_dept;
Now we can use the SQLite count () function as follows.
If we need to count the total number of rows at that time we can use the following statement as follows.
select count (*) from emp;Explanation
In the above example, we use the SQLite count () function, it returns all rows from the table even if null values exist in the column. The end out of the above statement we illustrated by using the following screenshot.
Now let’s see an example of SQLite count () with a group by clause as follows.
select count (*), dn.emp_dept_name from emp et, emp_dept dn
where et.emp_dept_id = dn.emp_dept_id
group by et.emp_dept_id;Explanation
In the above example we use where and group by clause also here we use an alias to the table and the result of the above statement is based on the expression that is emp count is based on the department wise. The end out of the above statement we illustrated by using the following screenshot.
Now implement SQLite count () with distinct clauses as follows.
select dn.emp_dept_name, count (distinct et.emp_id) from emp et, emp_dept dn
where et.emp_dept_id = dn.emp_dept_id
group by et.emp_dept_id;Explanation
Basically distinct is used to retrieve the unique records from the table, similarly, in SQLite, we can use the distinct with count () function to fetch the unique records from the table. The end out of the above statement we illustrated by using the following screenshot.
Now let’s see the example of SQLite count function with where clause as follows.
select dn.emp_dept_name, count (et.emp_id) from emp et, emp_dept dn
where et.emp_dept_id = dn.emp_dept_id and dn.emp_dept_name = 'COMP'
group by et.emp_dept_id;Explanation
In SQLite, we can use where clause with count () function to fetch the rows from the specified table and that based on the expression. Suppose we need to find how many employees work in a particular department at that time we can use where by clause. The end out of the above statement we illustrated by using the following screenshot.
Conclusion
We hope from this article you have understood about the SQLite count () function. From the above article, we have learned the basic syntax of count () function and we also see different examples of count () function. We also learned the rules of count () function. From this article, we learned how and when we use SQLite count () function.
Recommended Articles
We hope that this EDUCBA information on “SQLite COUNT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
