SQLite | SQLite Functions | Counting Rows in a Column or Table (count Function)
The count function counts rows in a specified column or table. This article explains how to use it.
Using the count Function
The syntax is as follows.
count(column_name)
Specify a column name or an asterisk (*). A column name counts rows whose value is not NULL; * counts every row in the table. With GROUP BY, the function returns a count for each group.
–
Create a table for this example.
create table user (id integer, name text, address text, gender text);
sqlite> create table user (id integer, name text, address text, gender text);
sqlite>
Insert the data.
insert into user values (1, 'devkuma', 'Seoul', 'Man');
insert into user values (2, 'kimkc', 'Busan', 'Woman');
insert into user values (3, 'arikuma', 'Suwan', 'Man');
insert into user values (4, 'happykuma', NULL, 'Man');
insert into user values (5, 'raccoon', 'Seoul', 'Woman');
sqlite> insert into user values (1, 'devkuma', 'Seoul', 'Man');
sqlite> insert into user values (2, 'kimkc', 'Busan', 'Woman');
sqlite> insert into user values (3, 'arikuma', 'Suwan', 'Man');
sqlite> insert into user values (4, 'happykuma', NULL, 'Man');
sqlite> insert into user values (5, 'raccoon', 'Seoul', 'Woman');
sqlite>
Count rows in the name and address columns.
select count(name), count(address) from user;
sqlite> select count(name), count(address) from user;
5|4
sqlite>
The address column has one NULL value, so its count is one less than the name count.
Count all rows in the table.
select count(*) from user;
sqlite> select count(*) from user;
5
sqlite>
Every table row is counted, even when all of its column values are NULL.
Finally, group the rows by gender and count each group.
select gender, count(*) from user group by gender;
sqlite> select gender, count(*) from user group by gender;
Man|3
Woman|2
sqlite>
When GROUP BY is used, rows are counted separately for each group. See Calculating Sums and Averages by Group (GROUP BY and HAVING) for more information.