Skip to main content

​
Intro

This guide introduces using non-aggregated columns in aggregated Beast Modes. When applying non-aggregated columns to aggregated Beast Modes, it is best to have a basic to mid-level understanding of SQL and access to Domo Support .

​
Use the GROUP BY Function

When building SQL queries that use aggregated functions and non-aggregated columns in the SELECT statement, all non-aggregated columns must be put in the GROUP BY statement of the query. The following DataSet contains a record of individual sales made by customers. The Customer ID column contains unique identifiers for each customer, the Ship Mode column contains two possible values: “Second Class” and “Standard Class,” and the Sales column contains the value amount sold to each customer on the order.

Order ID

Customer ID

Ship Mode

Sales

1

CG-12520

Second Class

261.98

2

CG-12520

Second Class

731.94

3

DV-13045

Second Class

14.62

4

SO-20335

Standard Class

957.5775

5

SO-20335

Standard Class

22.368

6

BH-11710

Standard Class

48.86

7

BH-11710

Standard Class

7.28

8

BH-11710

Standard Class

907.152

9

BH-11710

Second Class

18.504

10

BH-11710

Second Class

114.9

11

BH-11710

Standard Class

1706.184

12

BH-11710

Standard Class

911.424

13

AA-10480

Standard Class

15.552

14

IM-15070

Standard Class

407.976

15

HP-14815

Standard Class

68.81

16

HP-14815

Standard Class

2.544

17

PK-19075

Standard Class

665.88

18

AG-10270

Second Class

55.5

Using the table, we can build a single-value chart showing only the results of a Beast Mode: Total Sales that sums up the Sales column. The resulting query for the chart has the following format: 
SELECT SUM(`Sales`) AS `Total Sales` FROM DataSet
In the example, the only value in the SELECT statement is the aggregated Beast Mode Total Sales. There are no non-aggregated columns; therefore, no GROUP BY statements are required. If you want to build a bar chart for the Total Sales Beast Mode by Ship Mode, you need to get the Total Sales for each Ship Mode, resulting in the following query: 
SELECT `Ship Mode`, SUM(`Sales`) AS `Total Sales` FROM Dataset GROUP BY `Ship Mode`
The SELECT statement contains the aggregated Beast Mode, Total Sales, and the non-aggregated column, Ship Mode. So, the non-aggregated column, Ship Mode, needs to be in the GROUP BY statement.

​
Aggregations Using Non-Aggregated Columns

Aggregated Beast Modes that use non-aggregated columns can be harder to work with but can still executed using the following tips. In this example, we have a Pivot Table that displays the total sales by customer with the following Beast Mode function: 
Total Sales  
-----------  
SUM (`Sales`)
And results in the following query: 
SELECT `Customer ID,` SUM(`Sales`) AS `Total Sales` FROM DataSet GROUP BY `Customer ID`
The query includes a Beast Mode that is an aggregation in the SELECT statement and a non-aggregated column Customer ID. For the query to function correctly, Customer ID must be included in the GROUP BY statement. In our example scenario, orders shipped using Standard Class shipping cost 10% more than orders shipped using Second Class. When building your chart, you want the total sales using the Standard Class to be reduced by 10%; to do this, you can change the Total Sales Beast Mode to reduce sales using this shipping mode by 10%. Your updated Beast Mode would look like this: 
Total Sales  
-----------  
CASE  
   WHEN `Ship Mode` = `Standard Class` THEN SUM(`Sales` *.9)  
   WHEN 'Ship Mode` = `Second Class` THEN SUM(`Sales`)  
END
This Beast Mode is considered an aggregation because, in the THEN statement for each WHEN clause, you use the aggregating function SUM. The Ship Mode function is non-aggregated, so when rebuilding the query, your pivot table should look like this: 
SELECT `Customer ID`  
CASE  
  WHEN `Ship Mode` = `Standard Class` THEN SUM(`Sales` *.9)  
  WHEN `Ship Mode` = `Standard Class` THEN SUM(`Sales`)  
END AS `Total Sales`  
FROM DataSet GROUP BY `Customer ID,` `Ship Mode`
Even though the Ship Mode column is not used directly in the SELECT statement, it is still used in the SELECT statement in the non-aggregated form through the Beast Mode Total Sales. For the query to function, the column Ship Mode must be included in the GROUP BY statement. Your Pivot Table should now look like this:
A white sheet with linesDescription automatically generated
The Pivot Table data displays the Customer ID BH-11710, with a ***** error in the Total Sales column. This error occurs because the customer uses two different Ship Mode values in their orders. The resulting output includes two rows because the customer has values for both Standard Class and Second Class. This causes errors in the Pivot Table because it expects a single row instead of two. The Ship Mode column is not displayed because it is not included in the SELECT statement. Others viewing the table do not know the value that goes into the Ship Mode, nor can they see the actual Total Sales per customer.

​
Grand Total and Sub-total Rows on Tables

The compute Grand Total row on the Pivot Table must also include a second query that allows you to get the true Total Sales value for all customers. Typically, when computing “total” rows, we remove the specific column from the query, but in this case, we take the main query and remove the Customer ID. Because the Beast Mode uses a non-aggregated column, it must be added to the GROUP BY statement. If we remove the Customer ID column, we get this query: 
SELECT  
CASE  
   WHEN `Ship Mode` = `Standard Class` THEN SUM(`Sales` *.9)  
   WHEN `Ship Mode` = `Second Class` THEN SUM(`Sales`)  
END AS `Total Sales`  
FROM DataSet GROUP BY `Ship Mode`
This query retrieves the Total Sales for all customers. However, because this Beast Mode uses a non-aggregated column, we must still include that column in the GROUP BY statement. This query produces the following two rows of data:

Total Sales

1197.424

5721.6075

The two rows include one total for each Ship Mode value. For a total row value, we need to have a row that is the total of both values. We must put the original query into a subquery to get this value and then total the rows. 
SELECT SUM(subquery. `Total Sales`) FROM  
(SELECT `Customer ID`,  
CASE  
   WHEN `Ship Mode` = `Standard Class` THEN SUM(`Sales` *.9)  
   WHEN `Ship Mode` = `Second Class` THEN SUM(`Sales`)  
END AS `Total Sales`  
FROM DataSet GROUP BY `Customer ID,` `Ship Mode`) AS subquery
This query addresses the possibility of multiple rows per Customer ID and Ship Mode, so we must take the full table query, place it into a subquery, and compute a SUM based on the subquery.