SQL COUNT

The COUNT() function in SQL is used to count the number of rows in a table or the number of non-NULL values in a specific column. It is one of the most commonly used aggregate functions in SQL.

 

Syntax of SQL COUNT

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

• COUNT(): The aggregate function used to count rows or non-NULL values.
• column_name: The name of the column to count non-NULL values. If * is used, it counts all rows regardless of NULL values.
• table_name: The name of the table where the data is stored.
• condition: Optional condition to filter rows.

 

Key Points

• COUNT(*) counts all rows, including those with NULL values.
• COUNT(column_name) counts only non-NULL values in a column.
• The result is always an integer value.

 

Example of SQL COUNT

Consider the following table:

Table: Employees

To count the total number of employees:

SELECT COUNT(*) AS total_employees
FROM employees;

Output:

Explanation:

• COUNT(*) counts all rows in the employees table, resulting in 5 employees.

 

SQL COUNT with a Specific Column

To count the number of non-NULL salary values:

SELECT COUNT(salary) AS total_salaries
FROM employees;

Output:

Explanation:

• COUNT(salary) counts only non-NULL values in the salary column. If there were any NULL salary values, they would not be counted.

 

SQL COUNT with Conditions

You can apply a condition to count rows that meet a certain criterion. For example, to count the number of employees who earn more than 60,000:

SELECT COUNT(*) AS high_salary_count
FROM employees
WHERE salary > 60000;

Output:

Explanation:

• Only employees with a salary greater than 60,000 are counted. The result is 3 because John, Alice, and Sarah meet the condition.

 

SQL COUNT with Grouping

You can use COUNT() with the GROUP BY clause to count rows for different groups. For example, to count the number of employees in each department (assuming you have a department column):

SELECT department, COUNT(*) AS department_count
FROM employees
GROUP BY department;

Output:

Explanation:

• The COUNT() function counts the number of employees in each department, grouped by the department column.

 

SQL COUNT with DISTINCT

You can count distinct values in a column by using the DISTINCT keyword. For example, to count the number of unique salaries:

SELECT COUNT(DISTINCT salary) AS unique_salaries
FROM employees;

Output:

Explanation:

• The COUNT(DISTINCT salary) counts the number of unique salary values, ignoring duplicates.

 

Summary

The COUNT() function in SQL is used to count the number of rows or non-NULL values in a specified column. It can be used with conditions, DISTINCT, and GROUP BY for more advanced counting operations. The result is always an integer value, representing the number of rows or distinct values matching the query criteria.