Concept
Purpose
Grouping lets you process records with the same field value together.
Usage
There are two DQL operators that control grouping: in groups and in groups with group-totals. They perform identically; ?in groups with group-totals? is included for compatibility with previous versions of DataEase.
The in groups operator causes records with identical values in a specified field to be processed as a single group. The groups are automatically sorted in ascending order. For example, French Polynesian clubs are listed together as a group followed by the Haitian clubs, and so on.
Example 1
CITY .
The output generated by the script in Example 1 is grouped on the COUNTRY field (shown in bold):
Country |
Club Name |
City |
French Polynesia |
Bora Bora |
Papeete |
|
Moorea |
Moorea |
|
|
|
Haiti |
Magic Isle |
La Pointe aux Sables |
|
|
|
Mexico |
Cancun |
Yucatan |
|
Chichen Itza |
Chichen Itza |
|
Cholula |
Cholula |
|
Coba |
Coba |
|
Huatulco |
Oaxaca |
|
Ixtapa |
Guerrero |
|
Playa Blanca |
Jalisco |
|
Sonora Bay |
Sonora |
|
Teotihuacan |
Villa de Teotihuacan |
|
Uxmal |
Uxmal |
The in groups operator groups records and generates statistical totals for each group if any other statistic is requested by the script.
COUNTRY in groups ;
CITY ;
In this example, the count statistic allows DataEase to calculate the group total for each country. The line:
..tells DataEase to count every record that is processed.
The output includes the group level statistics (shown in bold):
Country |
Club Name |
City |
|
French Polynesia |
Bora Bora |
Papeete |
|
|
Moorea |
Moorea |
|
|
|
|
|
Group Total 2 |
|
|
|
Haiti |
Magic Isle |
La Pointe aux Sables |
|
Group Total 1 |
|
|
|
Mexico |
Cancun |
Yucatan |
|
|
Chichen Itza |
Chichen Itza |
|
|
Cholula |
Cholula |
|
|
Coba |
Coba |
|
|
Huatulco |
Oaxaca |
|
|
Ixtapa |
Guerrero |
|
|
Playa Blanca |
Jalisco |
|
|
Sonora Bay |
Sonora |
|
|
Teotihuacan |
Villa de Teotihuacan |
|
|
Uxmal |
Uxmal |
|
Group Total 10 |
|
|
|
... |
... |
... |
|
Total CLUBS: 25 |
|
|
|
There are two key points to remember when using Grouping:
Grouping automatically sorts groups in ascending alphabetical or numerical order (e.g., French Polynesian clubs before the Haitian clubs ).
Grouping operators must precede Sorting operators (in order and in reverse) in a script.
Product: Dataease [{8}]FIVE. Written by Jeyarajah Arulrajah 22/06/15 at 18:15:54
Product: Dataease [{8}]FIVE. Written by DataEase 22/06/15 at 18:53:38
Product: Dataease [{8}]FIVE. Written by Jeyarajah Arulrajah 22/06/15 at 21:53:07
Product: Dataease [{8}]FIVE. Written by DataEase 23/06/15 at 07:59:33
Product: Dataease [{8}]FIVE. Written by DataEase 23/06/15 at 09:25:04
Product: Dataease [{8}]FIVE. Written by Jeyarajah Arulrajah 23/06/15 at 12:52:17
Product: Dataease [{8}]FIVE. Written by Jeyarajah Arulrajah 23/06/15 at 13:27:46
Product: Dataease [{8}]FIVE. Written by Jeyarajah Arulrajah 23/06/15 at 13:34:58
Product: Dataease [{8}]FIVE. Written by DataEase 23/06/15 at 13:49:48
Product: Dataease [{8}]FIVE. Written by DataEase 23/06/15 at 13:55:56
Product: Dataease [{8}]FIVE. Written by Jeyarajah Arulrajah 23/06/15 at 14:00:31
Product: Dataease [{8}]FIVE. Written by DataEase 23/06/15 at 15:36:39
Product: Dataease [{8}]FIVE. Written by Jeyarajah Arulrajah 23/06/15 at 17:37:40
Product: Dataease [{8}]FIVE. Written by DataEase 23/06/15 at 18:17:56
Product: Dataease [{8}]FIVE. Written by DataEase 26/08/15 at 09:28:17
Product: Dataease [{8}]FIVE. Written by Kensington 26/08/15 at 09:52:12
Product: Dataease [{8}]FIVE. Written by DataEase 26/08/15 at 11:00:23
Product: Dataease [{8}]FIVE. Written by Kensington 27/08/15 at 09:23:32