In SQL, the COUNT() function is an aggregate function used to count the number of rows that meet a specified condition within a query. It operates on a set of rows and returns the number of rows that match the given condition. The condition can be applied to specific columns or the entire table.
Here's the basic syntax for using the COUNT() function:
SELECT COUNT(*)
FROM table_name
WHERE condition;
The COUNT() function can also be used with a specific column name inside it. For example:
SELECT COUNT(column_name) FROM table_name WHERE condition;
Here's an example to illustrate the usage of the COUNT() function:
Suppose we have a table named employees with columns employee_id, first_name, last_name, and department_id, and we want to count the number of employees in the IT department (assuming IT department has department_id 10):
SELECT COUNT(*) FROM employees WHERE department_id = 10;
In this example:
COUNT(*) counts all rows in the table where the department_id is 10.WHERE department_id = 10 specifies the condition for counting employees in the IT department.Additionally, you can use DISTINCT inside the COUNT() function to count only distinct values:
SELECT COUNT(DISTINCT column_name) FROM table_name;
This will count the number of distinct values in the specified column.
The COUNT() function is commonly used in SQL queries to determine the number of rows returned by a query or the number of non-null values in a column.