COUNT
COUNT function returns the number of unique values of an attribute within the given context.
COUNT Syntax
COUNT function can be used in the following three syntax forms:
| form | syntax | example |
|---|---|---|
| single parameter | SELECT COUNT(attribute) | SELECT COUNT(Order ID) |
| two parameters | SELECT COUNT(attribute, primary_key) | SELECT COUNT(Order ID, Campaign ID) |
| with USING | SELECT COUNT(attribute) USING primary_key | SELECT COUNT(Order ID) USING Campaign ID |
Single-Parameter Version
The single-parameter version of COUNT dynamically gets the context of where to count from the insight it is used in.
Two-Parameter Version
In the two-parameter version, the context where to count the attribute is determined explicitly by the second parameter - the primary key of the dataset.
The primary key is connection point between datasets. It connects the COUNT function’s first parameter to the dataset in which the count is to take place. For more information, see Connection Points in Logical Data Models.
Specifying COUNT Context Resolution with USING
You deploy the USING keyword in logical data models with ambiguous connection points.
The context for computation of COUNT may be ambiguous if there are multiple fact tables which relate to a counted attribute. Imagine a model with fact datasets Purchase_Fact and Sales_Fact that are both connected to the Store and Product attributes.
If you build an insight/report that displays the count of products per store with SELECT COUNT(Product) and slice it with Store, it would not compute because it is ambiguous if the insight/report displays the number of unique products that have been purchased by store or the number of unique products that have been sold by the store.
In a metric, USING provides a hint for which context should be used. For example the report will show the number of unique purchased products per store if Purchase_Fact attribute is placed into the USING clause SELECT COUNT(Product) USING Purchase_Fact.
The attribute in the USING clause does not need to be from the actual fact table, it can also be another attribute which uniquely determines the correct context (e.g. use of the Purchase Date attribute in the USING clause directs the use of the Purchase_Fact dataset to join Product with Store because Sales_Fact does not directly relate to Purchase Date).
COUNT in Specific XAE Versions
COUNT function’s behavior differs based on what version of the GoodData Extensible Analytics Engine (XAE) you have. To learn more, see XAE - Extensible Analytics Engine.
The single-parameter COUNT works since XAE version 3 environment (default for new users since October 2018). For more details on XAE3, see Upgrading XAE to Version 3.
The two-parameter version of COUNT is de facto required in the XAE1 environment. As COUNT is limited by the given attribute, it requires the second parameter to know where to count.
For example, COUNT(Product) can only be used in an insight/report with the Product. To count the ‘product sold’ and the ‘product in the warehouse’, you need two metrics.
Examples
The following chart shows the XAE3 usage of a single-parameter COUNT in SELECT COUNT(Order ID), sliced with Order Status, and stacked by Product Category.
The example is based on the logical data model outlined in GoodData Demo Workspace. As you can see, SELECT COUNT(Order ID) does not need another parameter to work despite the fact that it is not directly connected the dataset with Product Category.
In XAE1, you have to ‘connect’ the Order ID attribute with the rest of the model by specifying the second parameter in the COUNT function: SELECT COUNT(Order ID, Order Line ID).