ms-access Access SQL The COUNT() Function


Example

You can use the COUNT() function to return the number of records that match a query. The following 'Employee' table contains employee ID numbers and their associated manager's ID number.

Employee_IDManager_ID
1237
2237
3763
4245
4563
5745
5945
63

A COUNT() statement can be used to find out how many employees have a specific manager:

SELECT COUNT(*) AS CNT FROM Employees WHERE Employee.Manager_ID = 37;

returns

CNT

2

The function can also be combined in more complicated queries. To find out how many employees are directly supervised by a specified person, the following can be applied:

SELECT T1.Employee_ID,
    (SELECT COUNT(*) AS CNT FROM Employees AS T2 WHERE T2.Manager_ID =
        T1.Employee_ID) AS Supervised_Count
FROM Employees AS T1;

returns:

Employee_IDSupervised_Count
120
220
372
420
453
570
590
632

MSDN documentation may be found here.