Here we will learn what is SQLite COUNT() function and how to use SQLite COUNT() function with Where, Group By, DISTINCT clauses with examples.
In SQLite Count() Function is an aggregate function which is used to return the total number of rows in table based on the specified expression or conditions.
Generally, in SQLite Count() function will work with non-NULL numeric values to return number of times non-NULL values exists in column based on defined expression.
In SQLite Count() function, if we define expression as an asterisk character (*) then the COUNT function will return number of rows in table based on defined aggregate group or condition.
Following is the syntax of SQLite count() function to return the total number of rows available with defined expression.
SELECT COUNT(DISTINCT | ALL Expression) from tablename
[WHERE condition]
[GROUP BY Expression];
In above SQLite count() function syntax we defined multiple parameters those are
DISTINCT | ALL – By default SQLite count() function uses ALL to count the number of rows in table based on given expression so ALL is an Optional we don’t need to specify it separately. In case if we need to get the total number of DISTINCT values then we need to specify it with expression.
Expression – Its column or expression which we used to count number of non-NULL values available in table.
Tablename – Its name of the table which we wish to retrieve records from.
[WHERE Condition] – If we have any specific conditions to count number of rows available then we need to specify WHERE based on our requirements. It’s optional.
[Group By Expression] – If we need to count the number of rows available based on particular Expression value then we need to use Group By. It’s an optional.
Now we will see how to use 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 following query.
To create and insert some data in dept_master table execute following query.
CREATE TABLE dept_master
(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
dept_name TEXT);
INSERT INTO dept_master(dept_name)
VALUES('Admin'),
('Sales'),
('Quality Control'),
('Marketing');
Once we create and insert data execute following query to see the data of dept_master table.
sqlite> SELECT * FROM dep_master;
dept_id dept_name
---------- ----------
1 Admin
2 Sales
3 Quality Control
4 Marketing
Same way execute following queries to create and insert some data in emp_master table.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER);
INSERT INTO emp_master
values (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);
Now run the following query to check the records of emp_master table.
sqlite> SELECT * FROM emp_master;
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 total number of rows available in a table, including the rows which contains NULL values. The Count(*) will not take any parameters other than asterisk symbol (*).
Now we will see how to use SQLite count(*) on emp_master to get total number of records in table.
sqlite> SELECT COUNT(*) FROM emp_master;
count(*)
----------
9
If you observe above example it returns total number rows available in emp_master table. The above statement will return records even if NULL values exists in columns.
In SQLite we use Gropu By clause with Count() function to get the number of items exists in table based on Gropu By expression.
Suppose if I want to know how many employees exists in each department then By using SQLite Gropu By clause with Count() function we can easily get that information.
Following is the sqlite query to use Count() function with Gropu By clause to get the number of employees exists in each deparatement.
sqlite> SELECT COUNT(*),d.dept_name
FROM emp_master e, dept_master d
WHERE e.dept_id = d.dept_id
GROUP BY e.dept_id;
count(*) dept_name
---------- ----------
2 Admin
4 Sales
3 Quality Control
If you observe above example we got employees count based on the department wise.
Generally in SQLite we use DISTINCT clause to get the UNIQUE number of records from table, same way to get Unique number of records from table we use DISTINCT with Count() function.
Following is the SQLite Count() function with DISTINCT to get the unique number of employees count from each deparatement.
sqlite> SELECTd.dept_name, COUNT(DISTINCT e.emp_id)
FROM emp_master e, dept_master d
WHERE e.dept_id=d.dept_id
GROUP BY e.dept_id;
dept_name count(emp_id)
---------- ----------
Admin 2
Sales 4
Quality Control 3
If you observe above result we got the DISTINCT employees count based on the department id.
In SQLite we can use WHERE clause with COUNT() function to return the number of rows from table based on the defined conditions.
Suppose if i want to get number of employees from only “Admin” department then by using WHERE clause we can easily get count of employees only from “Admin” department.
Following is the SQLite query to use COUNT() function with WHERE clause to get employees count from only “Admin” department.
SELECT d.dept_name, COUNT(e.emp_id)
FROM emp_master e, dept_master d
WHERE e.dept_id=d.dept_id AND d.dept_name ='Admin'
GROUP BY e.dept_id;
dept_name count(*)
---------- ----------
Admin 2
If you observe above example we are getting count of employees whose department name equals to “Admin”.
This is how we can use COUNT() function in SQLite to return total number of rows based on our requirements.