Group-by queries
Group-by queries group the data for one measure by one dimension. You might use this query type to get a breakdown of Sales by Country, Profit by Segment, or COGS by Product. This type of query works with four different aggregation types.
This topic is organized as follows:
Starting assumptions
For each example in this topic:
The data is the sample dataset
The date is July 31, 2021
Default Time Filter =
Latest monthDefault Currency =
US Dollar (USD)
Group-by with sum aggregation
Aggregation type for measure | Aggregation words for query |
|---|---|
Sum | sum, total (optional) |
When your measure is sum-aggregated, Arria Answers calculates the sum-total value for each instance of the given dimension, and returns the values in descending order.
Query | Answer |
|---|---|
Tell me about Profit by Segment. | In July 2021, the Profit breakdown by Segment in descending order is Departmental ($1.4 million), Brand Store ($326,000), Channel Partners ($95,000), Chemists ($48,000) and Online (-$111,729). |
Tell me about Sales by Product. | In July 2021, the Sales breakdown by Product in descending order is Glower ($3.27 million), Nutrali ($3.07 million), Retine ($3.03 million), Camoide ($1.47 million), Karateon ($1.2 million) and Ointmo ($0.71 million). |
You can add some dimension instances if they are NOT from the group-by dimension.
Query | Answer |
|---|---|
Tell me about Sales by Product for Online. | In July 2021, the Sales breakdown by Product for Online in descending order is Karateon ($0.95 million), Retine ($0.57 million) and Nutrali ($319,000). |
Tip
You could filter the query further with a time filter or exclusion filter.
You can ask if the calculated sum totals are similar.
Query | Answer |
|---|---|
Are my Sales by Product values similar? | In July 2021, Products are dissimilar in terms of Sales. |
You can ask if the calculated sum totals are identical.
Query | Answer |
|---|---|
Are my Sales by Product values identical? | In July 2021, Products are not identical in terms of Sales. |
You can ask if the results set includes outliers.
Query | Answer |
|---|---|
Are there any outliers for Sales by Product? | In July 2021, no Products are outliers in terms of Sales. |
Tip
The outliers keyword works with sum-aggregated measures only.
Group-by with average aggregation
Aggregation type for measure | Aggregation words for query |
|---|---|
Average | average, mean (optional) |
For this type of query, you add just one dimension to the Arria Answers extension. This can be a time-dimension (e.g. Year) or non-time dimension (e.g. Country). What matters is that you remove all other dimensions from the extension.
Assuming that Year is the only dimension:
Query | Answer |
|---|---|
What is Avg(Sales) by Year? | Overall, the average values for Avg(Sales) by year are 2020 ($182,000), 2021 ($190,000) and 2022 ($182,000). |
Assuming that Product is the only dimension:
Query | Answer |
|---|---|
What is Avg(Sales) by Product? | Overall, the average values for Avg(Sales) by Product are Glower with $232,000, Ointmo with $218,000, Retine with $182,000, Karateon with $182,000, Camoide with $153,000 and Nutrali with $139,000. |
When you group by a time dimension (e.g. Year), the calculated average values appear in chronological order. When you group by a non-time dimension (e.g. Product), the values appear in descending numerical order.
Note that you can also ask if the average values are similar or identical.
Query | Answer |
|---|---|
Are my Avg(Sales) by Product values similar? | In July 2021, Products are dissimilar in terms of Avg(Sales). |
Are my Avg(Sales) by Product values identical? | In July 2021, Products are not identical in terms of Avg(Sales). |
Important
You must set the measure's aggregation type to Average in Tableau. This creates, for example, an Avg(Sales) measure that you must use in your query. You can give this measure an alias if you wish.
Group-by with maximum aggregation
Aggregation type for measure | Aggregation words for query |
|---|---|
Maximum | biggest, highest, largest, max, maximum, top (optional) |
When your measure is max-aggregated, Arria Answers finds the maximum value for each instance of the given dimension, and returns the highest of those values.
Query | Answer |
|---|---|
Tell me about Max(Sales) by Product. | In July 2021, the maximum entry for Max(Sales) by Product is Nutrali ($1.11 million). |
You can also ask about a specific number of values.
Query | Answer |
|---|---|
Top 3 values for Max(Sales) by Product? | In July 2021, the maximum 3 entries for Max(Sales) by Product are Nutrali ($1.11 million), Glower ($0.98 million) and Retine ($0.68 million). |
You can add some dimension instances if they are NOT from the group-by dimension.
Query | Answer |
|---|---|
Top 3 values for Max(Sales) in Canada by Product? | In July 2021, the maximum 3 entries for Max(Sales) in Canada by Product are Ointmo ($490,000), Nutrali ($487,000) and Glower ($398,000). |
Top 3 values for Max(Sales) for Online by Product? | In July 2021, the maximum 3 entries for Max(Sales) for Online by Product are Karateon ($285,000), Retine ($242,000) and Nutrali ($197,000). |
Tip
You could filter the query further with a time filter or exclusion filter.
You can also ask if the maximum values are similar or identical.
Query | Answer |
|---|---|
Are the Max(Sales) by Product values similar? | In July 2021, Products are dissimilar in terms of Max(Sales). |
Are the Max(Sales) by Product values identical? | In July 2021, Products are not identical in terms of Max(Sales). |
Important
You must set the measure's aggregation type to Maximum in Tableau. This creates, for example, a Max(Sales) measure that you must use in your query. You can give this measure an alias if you wish.
Group-by with minimum aggregation
Aggregation type for measure | Aggregation words for query |
|---|---|
Minimum | bottom, lowest, min, minimum, smallest (optional) |
When your measure is min-aggregated, Arria Answers finds the minimum value for each instance of the given dimension, and returns the lowest of those values.
Query | Answer |
|---|---|
Tell me about Min(Sales) by Country. | In July 2021, the minimum entry for Min(Sales) by Country is Mexico ($4,500). |
You can also ask about a specific number of values.
Query | Answer |
|---|---|
Bottom 3 values for Min(Sales) by Country? | In July 2021, the minimum 3 entries for Min(Sales) by Country are Mexico ($4,500), Germany ($4,900) and the United States of America ($7,600). |
You can add some dimension instances if they are NOT from the group-by dimension.
Query | Answer |
|---|---|
Bottom 3 values for Min(Sales) for Retine by Country? | In July 2021, the minimum 3 entries for Min(Sales) for Retine by Country are Canada ($18,000), Germany ($92,000) and Mexico ($236,000). |
Bottom 3 values for Min(Sales) for Online by Country? | In July 2021, the minimum 3 entries for Min(Sales) for Online by Country are Germany ($92,000), the France ($122,000) and Canada ($197,000). |
Tip
You could filter the query further with a time filter or exclusion filter.
You can also ask if the minimum values are similar or identical.
Query | Answer |
|---|---|
Are the Min(Sales) by Country values similar? | In July 2021, Countries are dissimilar in terms of Min(Sales). |
Are the Min(Sales) by Country values identical? | In July 2021, Countries are not identical in terms of Min(Sales). |
Important
You must set the measure's aggregation type to Minimum in Tableau. This creates, for example, a Min(Sales) measure that you must use in your query. You can give this measure an alias if you wish.
