Analytics SAQL Developer Guide

Overview
Quick Start
Examples
SAQL Reference
SAQL Basic Elements
SAQL Operators
SAQL Statements
arimax
cogroup
fill
filter
foreach
group-by
group-by rollup
join semi and anti
limit
load
offset
order
sample
timeseries
union
SAQL Functions
Query Performance
PDF

Note: This release is in preview. Features described here don’t become generally available until the latest general availability date that Salesforce announces for this release. Before then, and where features are noted as beta, pilot, or developer preview, we can’t guarantee general availability within any particular time frame or at all. Make your purchase decisions only on the basis of generally available products and features.

group-by

Organizes the rows returned from a query into groups. Within each group, you can apply an aggregate function, such as count() or sum() to get the number of items or sum, respectively.

Syntax

group-by takes this syntax.
1group data_stream by fields;
Parameter Description
data_stream Data input to group.
fields Fields by which data is grouped.

Group-by One Field

In this example, the query counts the number of rows for each Category field and groups the counts by category.
1q = load "Superstore";
2q = group q by 'Category';
3q = foreach q generate 'Category' as 'Category', count() as 'count';
4q = limit q 2000;
Category Count of Rows
Furniture 2,121
Office Supplies 6,026
Technology 1,847

cogroup and group-by are interchangeable. For clarity, we use group-by for statements that involve one data stream and cogroup for statements that involve two or more data streams.

Note

Group-by with Null Values

To return grouped null values in your queries, you must select the preference to include null values in Setup. Otherwise, queries ignore null values.

  1. In Setup, enter Analytics in the Quick Find box.
  2. Select Settings from the list of Analytics options.
  3. In Settings, click the checkbox for Include null values in Analytics queries.
Null handling preference in Setup

Here’s an example of a query that returns null values. It orders the results by the Sub_Category field and specifies that the results display in ascending order, with nulls first.

1q = load "Superstore";
2q = group q by 'Sub_Category';
3q = foreach q generate 'Sub_Category' as 'Sub_Category', count() as 'count';
4q = order q by 'Sub_Category' asc nulls first;
5q = limit q 2000;
Sub-Category Count of Rows
- 4
Accessories 775
Appliances 466
Art 796
Binders 1,523
Bookcases 228
Chairs 617
Copiers 68
Envelopes 254
Fasteners 217
Furnishings 957
Labels 364
Machines 115
Paper 1,370
Phones 889
Storage 846
Supplies 190
Tables 319

Group-by all

In this example, the query counts all of the rows and returns the number of different industries that you have opportunities with.

1q = load "DTC_Opportunity_SAMPLE";
2q = group q by all;
3q = foreach q generate unique('Industry') as 'unique_Industry';
# Unique of Industry
1 20