union
Combines multiple result sets into one result set. The result sets must have the same field names and structure. You can use a different dataset to create each result set, or you can use the same dataset.
You want to see how each rep compares to the average for deals won. You can make this comparison by appending these two result sets together:Then use union to append the two result sets.
- Total amount of opportunities won for each rep
- Average amount of opportunities won for all reps
First, show the total amount of won opportunities for each rep.
The resulting graph shows the sum of amount for each rep.
Next, calculate the average of the sum of the amounts for each rep using the average function.
Because the two data streams have the same field names and structure, you can use union to combine them.
The resulting graph contains the sum of amounts by each rep together with the average amount per rep.
Combine the SAQL fragments to get the complete SAQL statement.
Using a union statement and a join statement in the same query has strict enforcements. When a semi or anti join statement is present, the union statement returns an error if there are:
- mismatched number of columns
- mismatched data types
The errors appear as “Different number of fields found across union streams” or “Different types found for field 1: ‘fieldName’ in different union inputs.”
There’s also strict checking on the name of the columns. Using this SAQL example with a semi join statement and a union statement:
When the join is present, strict name checking only allows for one ‘Amount’ column name, which comes from the first stream, q1. The ‘Amount_total’ summary column name from the second stream, q2, isn’t honored and only the normal ‘Amount’ column name is in the union results. If the join statement is removed, both the normal ‘Amount’ and the summary ‘Amount_total’ column names are in the union results.
See Also