# Handling Alignment Grouping

An alignment grouping operation compares values of a field or an expression of members of the to-be-grouped set with members of a specified base set, and puts members matching a same member in the base set to same group. The result set will have same number of groups as the number of members in the base set. The alignment grouping operation may produce empty group(s) or leave one or more unmatching members.

## 1. Basic alignment grouping

### 1.1 Keep one matching members at most per group

Group records in a table by the order of a certain field and perform summarization by each group.

【Example 1】 Based on the associated SelectCourse table and Course table, find the unselected courses according to the order in Course table.

【SPL script】

A5’s result:

### 1.2 Keep all matching members per group

Group records in a table by the order of a certain field and perform aggregate on each group.

【Example 2】 Based on the associated EMPLOYEE table and DEPARTMENT table, calculate the number of employees in each department according to the order in DEPARTMENT table.

【SPL script】

A5’s result:

### 1.3 Create a new group for unmatching members

Group records in a table by the order of a certain field and put unmatching records into a new group.

【Example 3】 Based on the SALARY table (Below is a part of it), calculate the average salary for the states of [California, Texas, New York, Florida] and for other states as a whole, which are classified as “Other”.

【SPL script】

A5’s result:

## 2. Alignment grouping by sequence numbers

The sequence-number-based alignment grouping operation groups members in a set according to specified sequence numbers. It puts members of same sequence numbers in same group.

### 2.1 Keep one matching member at most per group

Find the records that are not referenced based on two associated table.

【Example 4】 Based on the associated Sales table and Customer table, list customers that have no orders in 2014.

【SPL script】

A6’s result:

### 2.2 Keep all matching records per group

Group records in a table by sequence numbers and perform aggregate on each group.

【Example 5】 Based on the following orders table (only a part of the data is shown), list the number of orders in each month of the year 2013.

【SPL script】

A4’s result:

### 2.3 Put a record in multiple groups

Get a sequence of sequence numbers and divide records by aligning records to it. A record could be put into more than one group during the process.

【Example 6】 Based on the post records table, group posts by tags and calculate the frequency of each tag. Below is part of the source table:

【SPL script】

A5’s result:

### 2.4 Group records by ranges

Divide records in a table into multiple segments according to ranges of values in a specified field, and perform aggregate on each group.

【Example 7】 Based on the salary table, group records according to salary ranges <8000, ≤8000 &≥12000, >12000, and calculate the number of employees in each group. Below is part of the source table:

【SPL script】

A5’s result:

The following task requires grouping records by specified ranges according to the expression result, and calculates average.

【Example 8】Based on the employee table, group records by ranges of hire durations, which are <10 years, ≥10 years &≤ 20 years, and ≥20 years, and calculate average salary in each group. Below is part of the source table:

【SPL script】

A6’s result:

## 3. Enumeration grouping

Enumeration grouping defines a set of enumerated conditions, calculates the conditions using members of the to-be-grouped set as parameters, and puts members making same condition true into one subset. There is a one-to-one relationship between the subsets in the result set and the enumerated conditions.

### 3.1 Put each member to the first matching group

Group records in a table according to the enumerated conditional expressions and put each record only in the first matching group.

【Example 9】 Based on the table recording population information in China’s major cities, group the cities by population. Below is a part of the source table:

【SPL script】

A4’s result:

### 3.2 Create a new group for unmatching members

Group records in a table according to the specified enumerated conditional expressions, and put unmatching records in a new group.

【Example 10】 Based on the employee table, group records by age groups: < 35 years and < 45 years (put unmatching ones to a new group), and calculate average salary in each group. Below is part of the source table:

【SPL script】

A5’s result:

### 3.3 Put a record in multiple groups

Group records in a table according to enumerated sequences and perform calculations on each group. A record could be put into more than one group during the process.

【Example 11】 Based on the GDP table, calculate the GDP per capita for direct-controlled municipalities, first-tier cities and second-tier cities respectively. Below is part of the source table:

【SPL script】

A5’s result:

Find more examples in SPL CookBook.