COUNT aggregates attribute values rather than metrics. It counts the number of unique values belonging to an attribute. In the following data model example, the total number of employees is computed with the COUNT operation:
There are some limitations to this approach. In the previous example, we have not identified where the count is to take place. The number of unique employee values may be different in each of the three data sets, since some employees may not be associated with any opportunities and other employees may never have made a call. As a result, the SELECT COUNT(Employee) metric should be broken down by the Name and Department attributes to derive meaningful values.
In this example, we measure the number of employees who have made a phone call. Since Facts of Call is connected to Employee in the logical data model, for every completed call, the employee who made it is also stored in the Facts of Call dataset. To count the number of unique Employee values within the Facts of Call dataset, a second parameter is inserted in our COUNT function to define the dataset where the count takes place.
To count the number of unique employee values in the Facts of Call dataset, the count metric is defined as follows:
SELECT COUNT (Employee, Facts_of_Call).
The first parameter in the COUNT syntax represents the attribute whose values you want to count. The second parameter specifies dataset in which those attribute values are counted.
The following data model example contains the (Customer Support) Tickets, Employees, and Payroll datasets. Ticket_ID and Salary each represent connection points from their respective datasets to the Employee dataset.
The total number of employees could be counted with a simple COUNT(Employee) metric. However, this metric cannot be used as the basis for counting the number of employees who have resolved a customer support ticket or the count the number of employees who are on payroll. A second parameter must be added to the COUNT operation in order to designate the data set where the COUNT is to place.
To count the number of employees who have responded to at least one customer support ticket, the following syntax should be used:
SELECT COUNT(Employee, Ticket_ID)
To count the number of employees who are on payroll, the following syntax should be used:
SELECT COUNT(Employee, Salary)
In this case, the values of the attribute Salary are identifiers that uniquely identify each employee who is on payroll.