SQLite COUNT

SQLite is a serverless database engine, it is written in C programming language and is easy to use and store the data. It is one of the most used database engines. It is widely used for the development of Embedded applications. In this article we will learn about the SQLite count, how it works and what are all the other functions that are used with SQLite to get the desired output.

SQLite COUNT

The function COUNT() is an aggregate function that returns the total number of items in a group based on the condition written and it also counts the null values if you do not specify the column name inside the count function.

• The count function is used for counting the number of records in a table.
• With the count function, we can count the desired field for the output.

Syntax:

COUNT ([ALL | DISTINCT] expression);

Explanation: As we know whatever the argument is passed into the function, the output is returned accordingly. The count is the keyword and the expressions include the column name or the other expressions.

Now a doubt arises about what it means by aggregate function and we will explain it below:

Aggregate Function

In DBMS, an aggregate function is a function where the values of the single column and multiple row values are processed together to form a single summation value. The aggregate functions are SUM(), COUNT(), MAX(), MIN(), and so on. All aggregate functions are used for the different operations.

For example, you can use the COUNT () function to get the number of employees from the employees table, or the number of departments from the departments table, and so on.

Example: To understand the SQLite COUNT Function in a more depth manner we need a table on which we will perform the operations. So will create a table called staff table which consists of emp_id, name, salary and dept as Columns. If you don’t know How to Create a Table to refer this.

let us consider the staff table and the fields in the table are emp_id, name, salary and dept with the 5 records inserted in it and you can find that the dept field for the emp_id 5 is Null because there is no data insered in it.

Staff Table

Example 1: Now we will try to count the no of records in the staff table.

Query:

select count( ) from staff;

Output:

Explanation: As, there are 5 records in the staff table and it is showing the count as 5 in the above output.

Count( ) Function Counts the NULL Values or Not?

Now let us count the dept column and check whether they counts the exact dept count or not. The syntax is select keyword followed by count and in it we specify the column name that to be counted and from followed by the table name from which the column is to be counted.

Query:

select count(dept ) from staff;

Output:

Explanation: In the above Query, we can see that it does not count the NULL values as the dept count is 5 but the output is 4, it is because as we have passed the column_name within the count function it does not count the NULL values.

Note: But, it counts the NULL values if you do not pass the column name inside the count function as it is shown below:

Query:

select count() dept
from staff;

Output:

Explanation: this is how it counts the NULL values too as the output is five, even the no of rows inserted are also five and the count is equal.

SQLite COUNT() with Distinct Function

It counts only the unique and non-null values. Unique values are nothing but the not repeated values.

Query:

select count(distinct dept) from faculty;

Output:

Explanation: As, we know there are three different departments they are cse, ece, eee and there is a null value for the dept column where Id is equal to 5. From the distinct function we have learned that It only counts the unique and non-null values and, such the ouput we got as 3.

SQLite COUNT (*)

Count (*) functionality is used to count all the rows from the table that you have chosen.

Let’s count the number of rows in the table.

Query

Select count (*) from faculty;

Output:

Explanation: the output looks like as below and there is a count of 5 records.

SQLite COUNT() with WHERE Clause

In SQLite, we use the WHERE clause with the Count() function to get the number of items that exist in a table based on the where expression. Basically, we used the WHERE Clause to apply the conditions on the operations.

Suppose we want to know how many faculty belong to cse department then by using the SQLite WHERE clause with the Count() function, we can quickly find out the information.

Query:

Select count ()
from faculty
WHERE dept = "cse";

Output:

Explanation: Here we have counted the total number of faculty that belong to cse.

SQLite COUNT() with GROUP BY Clause

The GROUP BY clause groups all records for each column that we need and then the count() function in conjunction with GROUP BY counts the number of records for each column name that we want to count.

Let’s Suppose we want all the departments, to count of each department for the number of count of the departments they belong to.

Query:

Select count (), dept
from faculty
group by dept;

Output:
Explanation: In the above Query, we have found the number of people in each department with the help of the GROUP BY Clause and COUNT() Function.

SQLite COUNT() with HAVING Clause

In the Having clause, we will specify the condition and based on that the result displayed. Having clause works on the group by clause and the conditions are applied to the groups that are created by group by clause.

Let’s find out department which has more than or equal to 2 students enrolled in it.

Query:

select count(), dept
from faculty
group by dept
having count(dept)>=2;

Output:

Explanation: Thus, one row is returned with the department count where the count of the department number is greater than or equal to 2.

Conclusion

After reading the whole article, we have good understanding of SQLite COUNT() Functions. We also have seen some examples with the COUNT() like Count with Group By, Having and WHERE Clause. We have also checked that the COUNT() Function can count the NULL value or not?