SQLite COUNT Aggregate Function

In SQLite, COUNT is an aggregate function that is used to count the number of rows returned by a SELECT statement. COUNT can be used with any data type, and it can also be used with the wildcard * to count all rows.

Syntax

The syntax for COUNT function in SQLite is:

SELECT COUNT(expression)
FROM table_name
WHERE condition;

Where expression can be a column, expression, or SQL statement, and table_name is the name of the table from which rows will be counted. The optional WHERE clause can be used to filter the rows.

Example

Suppose we have a table called employees with the following data:

id	name	age	salary
1	Alice	25	50000
2	Bob	30	60000
3	Charlie	35	70000
4	David	40	80000
5	Eve	45	90000

We can use the COUNT function to count the number of employees in the table:

SELECT COUNT(*)
FROM employees;

This will return the result:

COUNT(*)
5

We can also use the WHERE clause to count the number of employees who are older than 30:

SELECT COUNT(*)
FROM employees
WHERE age > 30;

This will return the result:

COUNT(*)
3

Output

The COUNT function returns a single value that represents the number of rows that match the specified condition.

Explanation

In the example above, we use the COUNT function to count the total number of rows in the employees table by using the wildcard (*) to count all rows. We then use the WHERE clause to count the number of employees who are older than 30.

Use

The COUNT function is commonly used to return the number of rows returned by a query or to calculate the number of records that match a certain condition. It can also be used in conjunction with other aggregate functions such as AVG, SUM, and MAX to calculate summary statistics for a table.

Important Points

• The COUNT function will count all rows by default if you use the wildcard *.
• The COUNT function does not count NULL values in the column or expression.
• You can use the DISTINCT keyword with the COUNT function to count the number of unique values in a column or expression.

Summary

The COUNT function in SQLite is a powerful tool for counting the number of rows returned by a query or the number of records that match a certain condition. It can be used in conjunction with other aggregate functions to calculate summary statistics for a table. Remember to use the DISTINCT keyword if you want to count unique values, and be aware that NULL values are not counted in the column or expression.