The SQL COUNT function is used to count the number of rows in a database table. It is an aggregate function that returns the number of items in a group.

Basic Usage:

The simplest use of COUNT is to count all rows in a table:


SELECT COUNT(*) FROM Employees;

Counting Specific Columns:

You can also use COUNT to count the number of non-NULL values in a specific column:


SELECT COUNT(Department) FROM Employees;

Using COUNT with DISTINCT:

To count distinct values in a column, use COUNT with DISTINCT:


SELECT COUNT(DISTINCT Department) FROM Employees;

Combining COUNT with WHERE Clause:

COUNT can be combined with a WHERE clause to count rows that meet specific conditions:


SELECT COUNT(*) FROM Employees WHERE Salary > 50000;

COUNT with GROUP BY:

Use COUNT with GROUP BY to get counts for each group of a column:


SELECT Department, COUNT(*) FROM Employees GROUP BY Department;

COUNT with HAVING Clause:

The HAVING clause can filter groups based on aggregate functions like COUNT:


SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 10;

COUNT with JOINs:

When using COUNT with JOINs, it can count rows across multiple tables:


SELECT Departments.Name, COUNT(Employees.ID) 
FROM Departments 
JOIN Employees ON Departments.ID = Employees.DepartmentID 
GROUP BY Departments.Name;

Advanced COUNT with Subqueries:

Subqueries can be used with COUNT for more complex queries:


SELECT Department, COUNT(*) 
FROM (SELECT Department, Salary FROM Employees WHERE Salary > 50000) AS HighEarners 
GROUP BY Department;

Console Output Example:

Finance: 15

HR: 8

IT: 20