Synergex.SynergyDE.Select.GroupBy

Group selection results (case sensitive)

WSupported on Windows
USupported on Unix
VSupported on OpenVMS
NSupported in Synergy .NET
namespace Synergex.SynergyDE.Select
public class GroupBy

The GroupBy class provides a collection of static methods for grouping a selection after the entire selection has been done. GroupBy provides a subset of the functionality of a SQL “GROUP BY” clause, which arranges identical data into groups and returns distinct records based on field specifications. If multiple records have the same values in specified fields, only the first one in field order will be returned.

Constructors

GroupBy

public static varargs GroupBy(field1, ...)

Sorts and groups records.

To build code with the GroupBy constructor, -qrntcompat must be 12030100 or higher.

Operators

The logical and operator (&& or .AND.) is supported and is equivalent to its Synergy counterpart. See Expressions for more information about this operator.

Discussion

The purpose of GroupBy is to aggregate results returned from a query based on shared values in specified fields. For example, if multiple records returned from a Select statement have the value “Sacramento” in the City field and GroupBy(“City”) is specified, all the records will be treated as a single record.

Changes to the file after making the selection may not be reflected in the records retrieved, especially if a record that matches the selection criteria is updated or inserted.

The specified fields must be in the record specified in the From class object. If they are not, an InvalidOperationException or $ERR_INVOPER error (“Field reference not entirely part of specified record”) will be generated at runtime.

GroupBy is case sensitive. If you need case-insensitive grouping of records, use Synergex.SynergyDE.Select.NoCaseGroupBy. GroupBy and NoCaseGroupBy can be combined if only some fields require case-insensitivity.

Currently, you cannot combine GroupBy and OrderBy in the same Select statement. Groups returned from a GroupBy query will be sorted in ascending order by default.

Because GroupBy only returns the first record in field order for each unique set of values in the specified fields, any fields in the record that are not specified in GroupBy belong to that record alone and may not represent the group as a whole. For example, if you have a file of employee records and specify GroupBy(Lastname), it may return a record with “Smith“ in the Lastname field and “Fred“ in the Firstname field. This record will represent all records where Lastname is “Smith” even if the file contains other records with different values for Firstname. The value of “Fred” may not be meaningful when dealing with the group as a whole.

Whenever you create a GroupBy object (or any Select object with a static cursor), you must also specify a Sparse object. If you don’t, an $ERR_INVO error will occur. Using Sparse can also help to distinguish meaningful fields in results returned by GroupBy. When you use Sparse in the Select statement and specify the same fields in Sparse that you specify in GroupBy, all fields that aren’t part of the GroupBy will be blanked out, leaving data only in the meaningful fields.

See also

Understanding the Synergy Select API

Examples

The following example which stores records to a file and performs three select queries on the file:

structure employee
    key,	  d5
    ,		  a1
    firstname,	  a15
    ,		  a1
    lastname,	  a15
    ,		  a1
    department,  a15
    ,		  a1
    salary,	  d7
endstructure

import Synergex.SynergyDE.Select

.main
record
    from,	@from
    selobj,	@select
    selobj2,   @select
    renum,	@RestrictedAlphaEnumerator
record rec
    data, employee
record rec2
    data, employee

record
    count,		i4
    salarysum,		d8
    salaryavg,		d7

.define FILE "DAT:employees.ism"
.proc
    xcall flags(7000000,1)
    open(1, o, "TT:")
    xcall isamc(FILE, ^size(rec), 1,
&		"START=1, LENGTH=5, NAME=""Key1"", DUPS, ASCEND, ATEND")
    open(2, u:I, FILE)
    store(2, "00001 Andrew          McDonald        Development     0025010")
    store(2, "00002 Bob             Brown           HR              0030020")
    store(2, "00003 Beth            Jones           HR              0035030")
    store(2, "00004 Cathy           Brown           Sales           0040040")
    store(2, "00005 David           Smith           HR              0045050")
    store(2, "00006 Fred            Smith           QA              0050060")
    store(2, "00007 George          Cartwright      Shipping        0055070")
    store(2, "00008 Henry           Smith           Development     0060080")
    store(2, "00009 Jennifer        Jones           QA              0065090")
    store(2, "00010 James           McDonald        Development     0070000")
    store(2, "00011 John            Cartwright      Development     0075010")
    store(2, "00012 Laura           Jones           QA              0080020")
    store(2, "00013 Matthew         Cartwright      Shipping        0085030")
    store(2, "00014 Phillip         Brown           Sales           0090040")
    store(2, "00015 Rose            Jones           Sales           0095050")
    close 2
    open(2, I:I, FILE)
    open(3, I:I, FILE)
    from = new from(2, rec)
    writes(1, "")

    writes(1, "OrderBy Department:")
    writes(1, "KEY   FIRSTNAME       LASTNAME        DEPARTMENT      SALARY")
    selobj = new select(from, OrderBy.Ascending(data.department))
    foreach rec in selobj
      begin
        writes(1,rec)
      end
    writes(1, "")

    writes(1, "GroupBy Department:")
    writes(1, "KEY   FIRSTNAME       LASTNAME        DEPARTMENT      SALARY")
    selobj = new select(new Sparse("*"),from, new GroupBy(data.department))
    foreach rec in selobj
      begin
        writes(1,rec)
      end
    writes(1, "")

    writes(1, "GroupBy Department with aggregate values:")
    writes(1, "DEPARTMENT         COUNT     AVG(SALARY)  SUM(SALARY)")
    selobj = new select(new Sparse(data.department), from, new GroupBy(data.department))
    foreach rec in selobj
      begin
        count = 0
        salarysum = 0
        salaryavg = 0
        selobj2 = new select(new from(3,rec2),(where)(rec2.data.department == rec.data.department))
        foreach rec2 in selobj2
          begin
            count += 1
            salarysum += rec2.data.salary
          end
          salaryavg = salarysum/count
          writes(1,rec.data.department + "    " + %string(count,"XX") +
          & "    " + %string(salaryavg,"$$$,$$$,$$$") +
          & "    " + %string(salarysum,"$$,$$$,$$$"))
      end
    writes(1, "")
    close 3
    close 2
    close 1
    stop
.end

The output from the program looks like this:

OrderBy Department:
KEY   FIRSTNAME       LASTNAME        DEPARTMENT      SALARY
00001 Andrew          McDonald        Development     0025010
00008 Henry           Smith           Development     0060080
00010 James           McDonald        Development     0070000
00011 John            Cartwright      Development     0075010
00002 Bob             Brown           HR              0030020
00003 Beth            Jones           HR              0035030
00005 David           Smith           HR              0045050
00006 Fred            Smith           QA              0050060
00009 Jennifer        Jones           QA              0065090
00012 Laura           Jones           QA              0080020
00004 Cathy           Brown           Sales           0040040
00014 Phillip         Brown           Sales           0090040
00015 Rose            Jones           Sales           0095050
00007 George          Cartwright      Shipping        0055070
00013 Matthew         Cartwright      Shipping        0085030

GroupBy Department:
KEY   FIRSTNAME       LASTNAME        DEPARTMENT      SALARY
00001 Andrew          McDonald        Development     0025010
00002 Bob             Brown           HR              0030020
00006 Fred            Smith           QA              0050060
00004 Cathy           Brown           Sales           0040040
00007 George          Cartwright      Shipping        0055070

GroupBy Department with aggregate values:
DEPARTMENT         COUNT     SUM(SALARY)  AVG(SALARY)
Development        04        $57,525      $230,100
HR                 03        $36,700      $110,100
QA                 03        $65,056      $195,170
Sales              03        $75,043      $225,130
Shipping           02        $70,050      $140,100