Using aggregate functions, groupBy clauses, and having clauses

Beginning with AR System  release 7.6.02, you can apply aggregate functions to the SELECT statement of a query, along with GROUPBY and HAVING clauses. These options can help to refine the selection of data retrieved from the database and reduce the need for in-memory processing of large numbers of records.

You can use the groupBy parameter in conjunction with an aggregate function in the getListFields parameter to define the set of results on which to apply the aggregate function, specifically MIN, MAX, SUM, AVG, or COUNT.

For example, if the application contains a table of ticket IDs, ticket statuses, and support engineer names, you could use the COUNT aggregate to generate an SQL query that would count and return the open tickets the for each support engineer (agent ):

SELECT COUNT(ticketId),agent FROM tickets WHERE status='Open' GROUP BY agent

To further refine the results of this query, you could also add a HAVING clause. The having parameter can also take an aggregate function. For example, to return records for only those support engineers who have more than ten open tickets, you would add a HAVING clause with a COUNT function:

SELECT COUNT(ticketId),agent FROM tickets WHERE status='Open' GROUP BY agent HAVING COUNT(ticketId) > 10

C example and exercise

This example illustrates some sample code that will generate a SELECT query using the COUNT aggregate function with GROUP BY and HAVING clauses.

The example code queries the field_dispprop form, an AR System Metadata form that stores information about display properties for fields and forms, to determine the number of records in which data is stored as a CLOB. For each distinct schema and view, this query determines the total number of display properties and the number of non-NULL short display properties. You could then subtract the short property count from the total to get the number of display properties that are stored as CLOBs rather than as regular text fields in field_dispprop.

Here is the example code:

static void
buildFieldId(ARMultiSchemaFieldIdStruct *ff,
      const char *a, ARInternalId fieldId)
{
   strcpy(ff->queryFromAlias, a);
   ff->fieldId = fieldId;
}
}
static void
buildFieldFunc(ARMultiSchemaFieldFuncStruct *ff,
      const char *a, ARInternalId fieldId, int funcId)
{
   strcpy(ff->queryFromAlias, a);
   ff->fieldId = fieldId;
   ff->funcId = funcId;
}
}
int
arschematest(ARControlStruct *ctrl, ARStatusList *status)
{
   int result = AR_RETURN_OK;
   static const char field_disppropName[]="AR System Metadata: field_dispprop";
   static const char field_disppropAlias[] = "field_dispprop";
   enum {
      F_recordId = 1,
      F_vuiId = 20017,
      F_schemaId = 20018,
      F_propShort = 20504
   };
   ARMultiSchemaFuncQueryFromList fromList = {0,};
   ARMultiSchemaFuncQueryFromStruct fromItem[1];
   unsigned int numMatches = 0, *numMatchesPtr = &numMatches;
   unsigned int numMatches = 0, *numMatchesPtr = &numMatches;
   /*
    * SELECT f.schemaId, f.vuiId, COUNT(f.recordId), COUNT(f.propShort),
    *    FROM field_dispprop f
    *    GROUP BY f.schemaId, f.vuiId
    *    HAVING COUNT(f.propShort) < COUNT(f.recordId)
    */
   fromItem[0].type = AR_MULTI_SCHEMA_SCHEMA_NAME;
   strcpy(fromItem[0].u.schemaName, field_disppropName);
   strcpy(fromItem[0].queryFromAlias, field_disppropAlias);
   fromItem[0].joinType = 0;
   fromItem[0].joinQual = NULL;
   fromList.numItems = 1;
   fromList.listPtr = fromItem;
   fromList.listPtr = fromItem;
   ARMultiSchemaFieldFuncStruct   fieldId[4];
   buildFieldFunc(&fieldId[0], field_disppropAlias,
         F_schemaId, AR_MULTI_SCHEMA_FUNC_NONE);
   buildFieldFunc(&fieldId[1], field_disppropAlias,
         F_vuiId, AR_MULTI_SCHEMA_FUNC_NONE);
   buildFieldFunc(&fieldId[2], field_disppropAlias,
         F_recordId, AR_MULTI_SCHEMA_FUNC_COUNT);
   buildFieldFunc(&fieldId[3], field_disppropAlias,
         F_propShort, AR_MULTI_SCHEMA_FUNC_COUNT);
   ARMultiSchemaFieldFuncList selectList = {0,};
   selectList.numItems = 4;
   selectList.listPtr = fieldId;
   selectList.listPtr = fieldId;
   ARMultiSchemaFieldIdStruct groupBy[2];
   ARMultiSchemaFieldIdList groupByList = {2, groupBy};
   buildFieldId(&groupBy[0], field_disppropAlias, F_schemaId);
   buildFieldId(&groupBy[1], field_disppropAlias, F_vuiId);
   buildFieldId(&groupBy[1], field_disppropAlias, F_vuiId);
   ARMultiSchemaFuncQualifierStruct having = {AR_COND_OP_NONE, NULL};
   ARMultiSchemaFuncRelOpStruct havingRel = {0,};
   having.operation = AR_COND_OP_REL_OP;
   having.u.relOp = &havingRel;
   {
      ARMultiSchemaFieldFuncValueOrArithStruct *ffv = &havingRel.operandLeft;
      havingRel.operation = AR_REL_OP_LESS;
      ffv->tag = AR_FIELD;
      buildFieldFunc(&ffv->u.fieldFunc,
            field_disppropAlias, F_propShort, AR_MULTI_SCHEMA_FUNC_COUNT);
      ffv = &havingRel.operandRight;
      ffv->tag = AR_FIELD;
      buildFieldFunc(&ffv->u.fieldFunc,
            field_disppropAlias, F_recordId, AR_MULTI_SCHEMA_FUNC_COUNT);
   }
   }
   ARMultiSchemaSortList        sortList = {0,};
   ARMultiSchemaFieldFuncValueListList msEntryList = {0, NULL};
   ARMultiSchemaFieldFuncValueListList msEntryList = {0, NULL};
   result = ARGetListEntryWithMultiSchemaFields(ctrl, &fromList,
               &selectList, NULL, &sortList,
               AR_START_WITH_FIRST_ENTRY, AR_NO_MAX_LIST_RETRIEVE,
               FALSE, &groupByList, &having, &msEntryList,
               numMatchesPtr, status);
   return result;
}

The generated SQL query is:

SELECT J0.C20018, J0.C20017, COUNT(J0.C1), COUNT(J0.C20504) FROM T87 J0 GROUP BY J0.C20018, J0.C20017 HAVING(COUNT(J0.C20504) < COUNT(J0.C1))

As an exercise, join the AR System Metadata: field_dispprop form with the AR System Metadata: arschema form to retrieve form names instead of or in addition to integer identifiers.