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

## Syntax

```nolang
result = union resultSetA, resultSetB [, resultSetC ...];
```

## Example

```nolang
q = union q1, q2, q3;
```

## Example

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.

```nolang
opt = load "DTC_Opportunity_SAMPLE";
opt = filter opt by 'Won' == "true";

-- group by owner
rep = group opt by 'Account_Owner';

-- project the sum of amount for each rep
rep = foreach rep generate 'Account_Owner' as 'Account_Owner', sum('Amount') as 'sum_Amount';

rep = order rep by 'sum_Amount' desc;
```

The resulting graph shows the sum of amount for each rep.

![Diagram showing the sum of amounts for each rep.](https://a.sfdcstatic.com/developer-website/sfdocs/analytics/media/bi_dev_guide_saql/bi_saql_union_ex\_1.png)

Next, calculate the average of the sum of the amounts for each rep using the `average` function.

```nolang
-- grouping rep by all returns all the data in a single row.
avg_rep = group rep by all;

-- Calculate the average of the Sum of Amount column.
-- Use the text ‘Average Deal Size’ in the ‘Account Owner’ column
avg_rep = foreach avg_rep generate "Average deal size" as 'Account_Owner', avg('sum_Amount') as 'sum_Amount';
```

Because the two data streams have the same field names and structure, you can use `union` to combine them.

```nolang
q = union rep, avg_rep;
```

The resulting graph contains the sum of amounts by each rep together with the average amount per rep.

![Diagram showing the sum of amounts for each rep, plus the average amount.](https://a.sfdcstatic.com/developer-website/sfdocs/analytics/media/bi_dev_guide_saql/bi_saql_union_ex\_2.png)

Combine the SAQL fragments to get the complete SAQL statement.

```nolang
opt = load "DTC_Opportunity_SAMPLE";
opt = filter opt by 'Won' == "true";

-- group by owner
rep = group opt by 'Account_Owner';

-- project the sum of amount for each rep
rep = foreach rep generate 'Account_Owner' as 'Account_Owner', sum('Amount') as 'sum_Amount';

rep = order rep by 'sum_Amount' desc;

-- grouping rep by all returns all the data in a single row.
avg_rep = group rep by all;


-- Calculate the average of the Sum of Amount column.
-- Use the text ‘Average Deal Size’ in the ‘Account Owner’ column
avg_rep = foreach avg_rep generate "Average deal size" as 'Account_Owner', avg('sum_Amount') as 'sum_Amount';

q = union rep, avg_rep;
```

## Considerations

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:

```soql
q1 = load "Opportunity";
q2 = load "Opportunity";

q1 =join q1 by (AccountId) semi, q3 by (AccountId);

q2 = group q2 by all;
q1 = foreach q1 generate q1.'AccountId' as 'AccountId',q1.StageName as 'StageName', q1.'Description' as 'Description', q1.'Id' as 'Id',q1.'Amount' as 'Amount', q1.'Probability' as 'Probability';
q2 = foreach q2 generate null as 'AccountId', null as 'StageName',null as 'Description',null as 'Id', sum(unique('Amount')) over([..] partition by all) as 'Amount_total',sum(unique('Probability')) over([..] partition by all) as 'Probability';
q1 = limit q1 5;

qU = union q1, q2;
```

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**

- [cogroup](/docs/analytics/bi-dev-guide-saql/guide/bi-saql-statement-cogroup.md)
- [join semi and anti](/docs/analytics/bi-dev-guide-saql/guide/bi-saql-statement-join-semi-anti.md)
- [Append Datasets using union](/docs/analytics/bi-dev-guide-saql/guide/bi-saql-examples-union.md)