# 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 month

• Default 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

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

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

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

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.

Top

## 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 visualization. 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 visualization.

Assuming that Year is the only dimension:

Query

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 MicroStrategy. 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. Top ## 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).

Query

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 MicroStrategy. 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. Top ## 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).

Query

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

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 MicroStrategy. 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.

Top