Counting unique field values in a set of rows
A field can contain duplicate values. Sometimes, you want to get the count of distinct values. For example, a table displays a list of customers and their countries, as shown in
Figure 10‑14. The table lists 12 customers from 4 different countries and a customer for which the country is not available.
Figure 10‑14 A table that lists customers and their countries
If you insert a data element that uses COUNT in the header or footer row of the table, COUNT returns 12, the number of rows in the table. However, if you want to get the number of countries, use COUNTDISTINCT instead.
In the example report, COUNTDISTINCT returns 5, not 4 as you might expect, because like the other aggregate functions, COUNTDISTINCT counts rows with null values. The third row in the table contains a null value for country. To get the real count of countries that are listed in the table, add a filter condition to the aggregation, as follows:
row["country"] != null
This condition counts only rows in which the country value is unique and not null.
