In this article, we will examine the COUNT() function in SQLite, its syntax, and its capabilities.
What is SQLite COUNT() Function?
To begin with, the SQLite COUNT() function is used to count the number of rows in a table or a result set that meets a specified criterion. The syntax of the COUNT() function is quite simple, with the function name COUNT, followed by a pair of parentheses that include an expression or a wildcard character. The syntax should appear like this:
The COUNT() function can be used in a SELECT statement, or any other SQL statement that returns a result set.
Create a Table and Insert Values
Let’s assume we have a table called employees.
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT,
salary INTEGER
);
Now, we insert some values in the employees table:
INSERT INTO employees (name, department, salary) VALUES ('Jake', 'Marketing', 65000);
INSERT INTO employees (name, department, salary) VALUES ('Mike', 'Sales', 55000);
INSERT INTO employees (name, department, salary) VALUES ('Emily', 'HR', 45000);
INSERT INTO employees (name, department, salary) VALUES ('David', 'Marketing', 76000);
Using the COUNT() Function in SQLite
The argument can be an expression, a column name, or an asterisk to count all rows in a table. The following query, for instance, will total the number of rows in the employees table:
The number of rows in the employees database will be shown in a single row as a result of this query.
The COUNT() function is also commonly used with the GROUP BY clause to count the number of rows in each group. The following query, for instance, will calculate the number of personnel in each department:
A list of departments and the number of employees in each department will be returned by this query.
The COUNT() function in SQLite is a powerful tool that can be used in a variety of ways. For instance, it may be used to determine how many entries satisfy a particular requirement. This can be done by including a WHERE clause in the query. The following query, for instance, will count the number of employees who make more than $50,000:
The number of employees that make more than $50,000 will be displayed in a single row as a result of this query.
The number of different values in a column may also be counted using the COUNT() method. The DISTINCT modifier can be used in this situation. The following query, for instance, will count the variety of departments in the employees table:
The number of separate departments in the employees database will be shown in a single row as a result of this query.
Conclusion
The COUNT() function in SQLite is a powerful tool that is widely used by developers to count the number of rows in tables and query results. It has a simple syntax and can be used in a variety of ways, including to count rows, count distinct values, count rows with a specific condition, and count the number of rows without retrieving them. This tutorial has effectively demonstrated the use of the COUNT() function through simple examples.
