---
schema_version: "1"
id: 9cf5371729dc1a610c465eb6a0a613ec6022dcc22c8068b47a20e818da46e17b
title: cogroup
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/analytics/bi_dev_guide_saql/guides/reference/bi-saql-statement-cogroup.md
canonical_url: https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-statement-cogroup.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 5a5ba013ef46a57023cf630ef97503978b07dcd029ae3ec606f8e5da967562d2
products:
  - Analytics SAQL Developer Guide
  - analytics
---

# cogroup

Use `cogroup` to blend data from two or more data streams into a single data stream. The data streams must have at least one common field.

`cogroup` is similar to relational database joins, but with some important differences. Unlike a relational database join, in a cogroup the datasets are grouped first, and then the groups are joined. You can use `cogroup` in these ways:

- inner `cogroup`
- left outer `cogroup`
- right outer `cogroup`
- full outer `cogroup`

**note:** The statements `cogroup` and `group` are interchangeable. For clarity, we use `group` for statements involving one data stream and `cogroup` for statements involving two or more data streams.

## Inner cogroup

Inner `cogroup` blends data from two or more data streams into a resulting data stream. The resulting data stream only contains values that exist in both data streams. That is, unmatched records are dropped.

**Syntax**

```nolang
result = cogroup data_stream_1 by field1, data_stream_2 by field2;
```

_field1_ and _field2_ must be the same type, but can have different names. For example, `q=group ops by 'Owner', quota by 'Name';`

## Example - Inner cogroup

Suppose that you want to understand how much time your reps spend meeting with each account. Is there a relationship between spending more time and winning an account? Are some reps spending much more or much less time than average? To answer these questions, first blend meeting data with account data using `cogroup`.

Suppose that you have a dataset of meeting information from the Salesforce Event object. In this example, your reps have had six meetings with four different companies. The Meetings dataset has a MeetingDuration column, which contains the meeting duration in hours.

![Diagram showing the meeting dataset.](https://developer.salesforce.com/media/bi_dev_guide_saql/bi_saql_cogroup_ds_meeting.png)

The account data exists in the Salesforce Opportunity object. The Ops dataset has an Account, Won, and Amount column. The Amount column contains the dollar value of the opportunity, in millions.

![Diagram showing the Ops datasets.](https://developer.salesforce.com/media/bi_dev_guide_saql/bi_saql_cogroup_ds_account.png)

To see the effect of meeting duration on opportunities, you start by blending these two datasets into a single data stream using `cogroup`.

```nolang
q = cogroup ops by 'Account', meetings by 'Company';
```

Internally (you cannot see these results yet), the resulting cogrouped data stream contains the following data. Note how the data streams are rolled up on one or more dimensions.

```nolang
(1,{(Shoes2Go,2),(Shoes2Go,5)},{(Shoes2Go,1,1.5),(Shoes2Go,0,3})

(2,{(FreshMeals,3),(FreshMeals, 1)},{(FreshMeals,1,2) (FreshMeals,1,1.4)})

(3,{(ZipBikeShare,4)},{(ZipBikeShare,1,1.1)})

(4,{(ZenRetreats,6)},{(ZenRetreats,0,2)})
```

Now the datasets are blended. To see the data, you create a projection using `foreach`:

```nolang
ops = load "Ops";
meetings = load "Meetings";
q = cogroup ops by 'Account', meetings by 'Company';
q = foreach q generate ops.'Account' as 'Account', sum(ops.'Amount') as 'Sum of Amount', sum(meetings.'MeetingDuration') as 'TimeSpent';
```

The resulting data stream contains the sum of amount and total meeting time for each company. The sum of amount is the sum of the dollar value for every opportunity for the company.

![Diagram showing the blended dataset.](https://developer.salesforce.com/media/bi_dev_guide_saql/bi_saql_cogroup_ds_result.png)

Now that you have blended the data into a single data stream, you can analyze the effects that total meeting time has on your opportunities.

## Left Outer cogroup

Left outer `cogroup` blends data from the right data stream with the left data stream. The resulting data stream only contains values that exist in the left data stream. If the left data stream has a value that the right data stream does not, the missing value is null in the resulting data stream.

**tip:** Use `coalesce` to replace a null value with the value of your choice. See [Example: Left Outer Cogroup with `coalesce()`](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/concept/bi-saql-examples-coalesce.md).

**Syntax**

```nolang
result = cogroup data_stream_1 by field1 left, data_stream_2 by field2;
```

_field1_ and _field2_ must be the same type, but can have different names. For example, `q=group ops by 'Owner' left, quota by 'Name';`

## Example - Left Outer cogroup With coalesce

Suppose that you want to see what percentage of quota that your reps have obtained. Your quota dataset shows each employee's quota (notice that Farah does not have a quota):

![Quota dataset.](https://developer.salesforce.com/media/bi_dev_guide_saql/bi_saql_cogroup_ds_quota.png)

Your opportunities data shows the opportunity amount that each employee has won (notice that Jonathan does not have a won opportunity).

![Opportunities dataset.](https://developer.salesforce.com/media/bi_dev_guide_saql/bi_saql_cogroup_ds_ops.png)

Use a left outer `cogroup` to show only employees that have quotas. Also show the percentage of quota attained.

```nolang
quota = load "Quota";
opp = load "Opportunity";
q = group quota by 'Employee' left, opp by 'Employee';
q = foreach q generate quota.'Employee' as 'Employee', trunc(sum(opp.'Amount')/sum(quota.'Quota')*100, 2) as 'Percent Attained';
```

Jonathan has not won any opportunities yet, so his percent attained is null.

![Opportunities dataset.](https://developer.salesforce.com/media/bi_dev_guide_saql/bi_saql_cogroup_ds_jonNull.png)

Use `coalesce` to replace the null opportunities with a zero.

```nolang
quota = load "Quota";
opp = load "Opportunity";
q = group quota by 'Employee' left, opp by 'Employee';
q = foreach q generate quota.'Employee' as 'Employee', trunc(coalesce(sum(opp.'Amount'),0)/sum(quota.'Quota')*100, 2) as 'Percent Attained';
```

Now Jonathan's percent attained is displayed as zero.

![Opportunities dataset.](https://developer.salesforce.com/media/bi_dev_guide_saql/bi_saql_cogroup_jonzero.png)

## Right Outer cogroup

Right outer `cogroup` blends data from the left data stream with the right data stream. The resulting data stream only contains values that exist in the right data stream. If the right data stream has a value that the left data stream does not, the missing value is null in the resulting data stream.

**tip:** Use `coalesce` to replace a null value with the value of your choice. See [Example: Right Outer Cogroup with `coalesce()`](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/concept/bi-saql-examples-coalesce.md).

**Syntax**

```nolang
result = cogroup data_stream_1 by field1 right, data_stream_2 by field2;
```

_field1_ and _field2_ must be the same type, but can have different names. For example, `q=group ops by 'Owner' right, quota by 'Name';`

## Full Outer cogroup

Full outer `cogroup` blends data from the left and right data streams. The resulting data stream contains all values. If one data stream has a value that the other data stream does not, the missing value is null in the resulting data stream.

**tip:** Use `coalesce` to replace a null value with the value of your choice.

**Syntax**

```nolang
result = cogroup data_stream_1 by field1 full, data_stream_2 by field2;
```

_field1_ and _field2_ must be the same type, but can have different names. For example, `q=group ops by 'Owner' full, quota by 'Name';`

**See Also**

- [union](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-statement-union.md)
- [join semi and anti](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-statement-join-semi-anti.md)
- [Combine Data from Multiple Data Streams with cogroup](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/concept/bi-saql-examples-cogroup.md)
- [Replace Null Values with coalesce()](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/concept/bi-saql-examples-coalesce.md)
- [group-by](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-statement-groupby.md)