# How to do a simple math on aggregate values from different streams in SAQL?

This should be some basic stuff but lack of good documentation on SAQL is making it hard to figure out. I just want to do a simple math like ths-

```Avg. Sale Price = Total Sale Price/Qty
```

The filters used for computing Total Sale Price and Qty are different. For example,

```a = load ...
b = filter a by ... //some filters here
b = group b by all;
b = foreach b generate sum('Sale_Price') as 'total_sale_price';

c = filter a by ... //some filters here (different from those used for b)
c = group c by all;
c = foreach c generate count() as 'qty';
```

Now, I just want to compute b.total_sale_price/c.qty. How can I do that?

• Just reading some of the documentation ? developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_eql.meta/… -- Seeing how b = Total_sales_price and c = qty... could you not do d = b/c? Or try d = Total_Sales_price/qty? Jun 17, 2016 at 12:07
• b and c are streams (like tables), so what you're saying is b.total_sale_price/c.qty That doesn't work because the fields are not part of same stream. One is from b and another from c Jun 17, 2016 at 19:24

You can also do a cogroup on two different data streams. This is also useful for Year over Year calculations. The one challenge with a cogroup is that the fields that you are cogrouping must be the same (Similar to an inner join).

``````a = load ..
b = filter a by ..
b = group b by 'Join_Field_1';
b = foreach b generate 'Join_Field_1' as 'Join_Field_1', sum('Sale_Price') as 'total_sale_price';

d = filter c by (different filters)
d = group d by 'Join_Field_2';
d = foreach d generate 'Join_Field_2' as 'Join_Field_2', count() as 'qty';

cg = cogroup b by 'Join_Field_1', d by 'Join_Field_2';
res = foreach cg generate sum(b.'total_sale_price')/sum(d.'qty') as 'sum_avg_sale_price'
``````

Looks like it can be done using pseudo fields. There's probably a better way but this works..

```
b = foreach b generate sum('Sale_Price') as 'total_sale_price', 0 as 'qty';

c = foreach c generate 0 as 'total_sale_price', count() as 'qty';

d = union b, c; //pseudo fields allow you to do a union
d = group d by all;
d = foreach d generate sum('total_sale_price')/sum('qty') as 'avg_sale_price';
```

You can use Windowing Functions

From the Percentage Total example in the documentation

``````q = load "dataset";
q = group q by (OrderDate_Year, OrderDate_Quarter);
q = foreach q generate OrderDate_Year as Year, OrderDate_Quarter as Quarter, sum(Sales) as sumSales, (sum(Sales) * 100) / sum(sum(Sales)) over([..] partition by OrderDate_Year) as p_tot;
``````
• sum(Sales) is the total Sales amount per year per Quarter
• sum(sum('Sales') is the total Sales amount for Quarters in the current Year
• sum(Sales) * 100 / sum(sum('Sales') calculates the percentage that Quarter contributed to the total Sales amount in the current Year
• [..] means "include all records in the partition"