Aggregate SQL Translation Examples

Internal reference showing how PsoftQL aggregate requests translate to SQL, with real response bodies for the most-used patterns

Categories:

Examples

This document shows how PsoftQL aggregate requests translate to SQL queries — and what comes back from the wire. It is both an internal reference for implementation and a guide for callers assembling aggregate requests. Each example below shows three things: the PsoftQL request, the SQL it generates, and the actual JSON response captured against a live PeopleSoft Campus Solutions 9.2 / PeopleTools 8.61 environment. For the response shape in the abstract — objectType: "aggregateResult", results array instead of fields, the top-level isAggregate and totalGroups markers — see Aggregate Response Structure.

Example 1: Simple COUNT with GROUP BY

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "role_count"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLETYPE,
    COUNT(1) AS role_count
FROM PS_PSROLEDEFN A
GROUP BY A.ROLETYPE
ORDER BY A.ROLETYPE

Response

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": ["ROLETYPE"],
      "aggregateFields": ["role_count"],
      "results": [
        { "ROLETYPE": "Q", "role_count": 14 },
        { "ROLETYPE": "U", "role_count": 252 }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "isAggregate": true,
  "totalGroups": 2,
  "productVersion": "2026-04-20"
}

Example 2: COUNT with Criteria (WHERE clause)

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "criteriaFields": [
        {
          "fieldName": "ROLESTATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "active_role_count"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLETYPE,
    COUNT(1) AS active_role_count
FROM PS_PSROLEDEFN A
WHERE A.ROLESTATUS = 'A'
GROUP BY A.ROLETYPE
ORDER BY A.ROLETYPE

Response

On this demo database every role is active, so the counts match Example 1 — but adding the filter is what guarantees that, in a database with both Active and Inactive roles, you’d only see the active ones in the result:

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": ["ROLETYPE"],
      "aggregateFields": ["active_role_count"],
      "results": [
        { "ROLETYPE": "Q", "active_role_count": 14 },
        { "ROLETYPE": "U", "active_role_count": 252 }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 2
}

Example 3: Multiple GROUP BY Fields

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE", "ROLESTATUS"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "role_count"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLETYPE,
    A.ROLESTATUS,
    COUNT(1) AS role_count
FROM PS_PSROLEDEFN A
GROUP BY A.ROLETYPE, A.ROLESTATUS
ORDER BY A.ROLETYPE, A.ROLESTATUS

Response

Each row in results represents one unique combination of the GROUP BY fields. With two groupings of size 2 there are up to 4 possible buckets — the demo database happens to have data in 2 of them:

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": ["ROLETYPE", "ROLESTATUS"],
      "aggregateFields": ["role_count"],
      "results": [
        { "ROLETYPE": "Q", "ROLESTATUS": "A", "role_count": 14 },
        { "ROLETYPE": "U", "ROLESTATUS": "A", "role_count": 252 }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 2
}

Example 4: Multiple Aggregate Functions

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "JOB",
      "criteriaFields": [
        {
          "fieldName": "EMPL_STATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["DEPTID"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "employee_count"
          },
          {
            "function": "SUM(ANNUAL_RT)",
            "outputLabel": "total_salary"
          },
          {
            "function": "AVG(ANNUAL_RT)",
            "outputLabel": "avg_salary"
          },
          {
            "function": "MIN(ANNUAL_RT)",
            "outputLabel": "min_salary"
          },
          {
            "function": "MAX(ANNUAL_RT)",
            "outputLabel": "max_salary"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.DEPTID,
    COUNT(1) AS employee_count,
    SUM(A.ANNUAL_RT) AS total_salary,
    AVG(A.ANNUAL_RT) AS avg_salary,
    MIN(A.ANNUAL_RT) AS min_salary,
    MAX(A.ANNUAL_RT) AS max_salary
FROM PS_JOB A
WHERE A.EMPL_STATUS = 'A'
GROUP BY A.DEPTID
ORDER BY A.DEPTID

Response (first 4 of many groups, trimmed for readability via `rowLimit: 5`)

All five aggregate functions are evaluated for each group. Numeric fields come back as JSON numbers (not strings); preserve precision in your client by parsing into a decimal type rather than a 64-bit float when the values can exceed safe-integer range — note max_salary of 192132000 in row 2:

{
  "data": {
    "JOB": {
      "objectType": "aggregateResult",
      "objectName": "JOB",
      "groupByFields": ["DEPTID"],
      "aggregateFields": ["employee_count", "total_salary", "avg_salary", "min_salary", "max_salary"],
      "results": [
        {
          "DEPTID": "1000",
          "employee_count": 7,
          "total_salary": 2155000,
          "avg_salary": 307857.1428571429,
          "min_salary": 180000,
          "max_salary": 490000
        },
        {
          "DEPTID": "10000",
          "employee_count": 827,
          "total_salary": 602910821.362,
          "avg_salary": 729033.6413083434,
          "min_salary": 0,
          "max_salary": 192132000
        },
        {
          "DEPTID": "10001",
          "employee_count": 11,
          "total_salary": 183759.835,
          "avg_salary": 16705.43954545455,
          "min_salary": 1920,
          "max_salary": 36813
        }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 5
}

Example 5: No GROUP BY (Total Aggregation)

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "aggregateConfig": {
        "groupByFields": [],
        "aggregateFields": [
          {
            "function": "COUNT(*)",
            "outputLabel": "total_roles"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    COUNT(*) AS total_roles
FROM PS_PSROLEDEFN A

Note: No GROUP BY clause, no ORDER BY clause when there are no groupByFields.

Response

When there is no grouping the results array always has exactly one element. totalGroups is 1 even though there’s no real grouping happening — the single global aggregate is treated as one degenerate group:

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": [],
      "aggregateFields": ["total_roles"],
      "results": [
        { "total_roles": 266 }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 1
}

Example 6: COUNT DISTINCT

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "STDNT_ENRL",
      "criteriaFields": [
        {
          "fieldName": "STDNT_ENRL_STATUS",
          "fieldValue": "E",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["INSTITUTION", "STRM"],
        "aggregateFields": [
          {
            "function": "COUNT(DISTINCT EMPLID)",
            "outputLabel": "unique_students"
          },
          {
            "function": "COUNT(1)",
            "outputLabel": "total_enrollments"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.INSTITUTION,
    A.STRM,
    COUNT(DISTINCT A.EMPLID) AS unique_students,
    COUNT(1) AS total_enrollments
FROM PS_STDNT_ENRL A
WHERE A.STDNT_ENRL_STATUS = 'E'
GROUP BY A.INSTITUTION, A.STRM
ORDER BY A.INSTITUTION, A.STRM

Response (first 3 groups via `rowLimit: 3`)

Note unique_students (DISTINCT EMPLID) versus total_enrollments (raw COUNT(1)) — the gap tells you how many students were enrolled in more than one class for that term:

{
  "data": {
    "STDNT_ENRL": {
      "objectType": "aggregateResult",
      "objectName": "STDNT_ENRL",
      "groupByFields": ["INSTITUTION", "STRM"],
      "aggregateFields": ["unique_students", "total_enrollments"],
      "results": [
        { "INSTITUTION": "GLAKE", "STRM": "0370", "unique_students": 1, "total_enrollments": 2 },
        { "INSTITUTION": "GLAKE", "STRM": "0410", "unique_students": 1, "total_enrollments": 1 },
        { "INSTITUTION": "GLAKE", "STRM": "0430", "unique_students": 1, "total_enrollments": 2 }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 3
}

Example 7: With Effective Date Logic

When noEffectiveDateLogic is false (default) and the record has EFFDT:

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL",
      "aggregateConfig": {
        "groupByFields": ["INSTITUTION"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "org_count"
          }
        ]
      }
    }
  ]
}

Generated SQL (Oracle)

SELECT
    A.INSTITUTION,
    COUNT(1) AS org_count
FROM PS_ACAD_ORG_TBL A
WHERE A.EFFDT = (
    SELECT MAX(A2.EFFDT)
    FROM PS_ACAD_ORG_TBL A2
    WHERE A2.ACAD_ORG = A.ACAD_ORG
    AND A2.EFFDT <= SYSDATE
)
GROUP BY A.INSTITUTION
ORDER BY A.INSTITUTION

Response (first 4 of 12 institutions)

The correlated sub-select restricts each row to its current effective-dated version before grouping, so org_count is the count of currently effective org rows per institution — not the count of every historical version. Set noEffectiveDateLogic: true to count every historical row instead.

{
  "data": {
    "ACAD_ORG_TBL": {
      "objectType": "aggregateResult",
      "objectName": "ACAD_ORG_TBL",
      "groupByFields": ["INSTITUTION"],
      "aggregateFields": ["org_count"],
      "results": [
        { "INSTITUTION": "GLAKE", "org_count": 18 },
        { "INSTITUTION": "PSAUS", "org_count": 25 },
        { "INSTITUTION": "PSCCS", "org_count": 37 },
        { "INSTITUTION": "PSESP", "org_count": 26 }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 12
}

Example 8: With rowLimit (Top N)

PsoftQL Request

{
  "isAggregate": true,
  "rowLimit": 10,
  "records": [
    {
      "recordName": "JOB",
      "criteriaFields": [
        {
          "fieldName": "EMPL_STATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["DEPTID"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "employee_count"
          }
        ]
      }
    }
  ]
}

Generated SQL (Oracle 12c+)

SELECT
    A.DEPTID,
    COUNT(1) AS employee_count
FROM PS_JOB A
WHERE A.EMPL_STATUS = 'A'
GROUP BY A.DEPTID
ORDER BY A.DEPTID
FETCH FIRST 10 ROWS ONLY

Generated SQL (SQL Server)

SELECT TOP 10
    A.DEPTID,
    COUNT(1) AS employee_count
FROM PS_JOB A
WHERE A.EMPL_STATUS = 'A'
GROUP BY A.DEPTID
ORDER BY A.DEPTID

Response

Same shape as Example 4 but capped at 10 groups. The response’s totalGroups reflects the truncated count, not the underlying total — so for true “top N” queries you typically add an explicit ORDER BY (via orderByFields) on the aggregated column, otherwise rowLimit just trims the first N alphabetical DEPTID buckets.

Example 9: With sqlWhereClause

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "sqlWhereClause": "A.ROLENAME LIKE 'SA%' AND A.ROLESTATUS = 'A'",
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "role_count"
          },
          {
            "function": "MAX(LASTUPDDTTM)",
            "outputLabel": "last_updated"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLETYPE,
    COUNT(1) AS role_count,
    MAX(A.LASTUPDDTTM) AS last_updated
FROM PS_PSROLEDEFN A
WHERE A.ROLENAME LIKE 'SA%' AND A.ROLESTATUS = 'A'
GROUP BY A.ROLETYPE
ORDER BY A.ROLETYPE

Response

The MAX(LASTUPDDTTM) aggregate returns the most recent edit timestamp for each group as a string in PeopleSoft’s standard format. Mixing a numeric aggregate (COUNT) with a non-numeric one (MAX on a datetime) in the same request is fine — each column is computed independently:

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": ["ROLETYPE"],
      "aggregateFields": ["role_count", "last_updated"],
      "results": [
        { "ROLETYPE": "U", "role_count": 2, "last_updated": "2007-07-11-16.45.39.000000" }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 1
}

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEUSER",
      "aggregateConfig": {
        "groupByFields": ["ROLENAME"],
        "aggregateFields": [
          {
            "function": "COUNT(DISTINCT ROLEUSER)",
            "outputLabel": "user_count"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLENAME,
    COUNT(DISTINCT A.ROLEUSER) AS user_count
FROM PS_PSROLEUSER A
GROUP BY A.ROLENAME
ORDER BY A.ROLENAME

Response (first 5 of many groups via `rowLimit: 5`)

{
  "data": {
    "PSROLEUSER": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEUSER",
      "groupByFields": ["ROLENAME"],
      "aggregateFields": ["user_count"],
      "results": [
        { "ROLENAME": "ACM Administrator",         "user_count": 1 },
        { "ROLENAME": "AG Composer Administrator", "user_count": 1 },
        { "ROLENAME": "AG Composer User",          "user_count": 1 },
        { "ROLENAME": "AWE Administrator",         "user_count": 2 },
        { "ROLENAME": "AppServer Administrator",   "user_count": 4 }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 5
}

Implementation Notes

SQL Generation Pattern

SELECT
    {groupByFields with A. prefix},
    {aggregateFields with A. prefix on field references}
FROM PS_{recordName} A
WHERE {criteriaFields converted to SQL}
    {AND sqlWhereClause if provided}
    {AND effective date logic if applicable}
GROUP BY {groupByFields with A. prefix}
ORDER BY {groupByFields with A. prefix}
{FETCH FIRST rowLimit ROWS ONLY if rowLimit provided}

Field Prefix Handling

The aggregate function string needs to be parsed to add the A. alias prefix to field names:

Input	Output
`COUNT(*)`	`COUNT(*)` (no change)
`COUNT(1)`	`COUNT(1)` (no change)
`COUNT(EMPLID)`	`COUNT(A.EMPLID)`
`COUNT(DISTINCT EMPLID)`	`COUNT(DISTINCT A.EMPLID)`
`SUM(ANNUAL_RT)`	`SUM(A.ANNUAL_RT)`
`AVG(SALARY)`	`AVG(A.SALARY)`
`MIN(HIRE_DT)`	`MIN(A.HIRE_DT)`
`MAX(LASTUPDDTTM)`	`MAX(A.LASTUPDDTTM)`

Validation Checks

If isAggregate: true and pageNumber > 1 → Error
If isAggregate: true and multiple records with parentRecordName → Error
If aggregateConfig missing on the record when isAggregate: true → Error
If aggregateFields empty when isAggregate: true → Error
Validate field names in groupByFields exist on the record
Parse and validate field names in function expressions exist on the record (except * and literals like 1)

Aggregate SQL Translation Examples

Example 1: Simple COUNT with GROUP BY

PsoftQL Request

Generated SQL

Response

Example 2: COUNT with Criteria (WHERE clause)

PsoftQL Request

Generated SQL

Response

Example 3: Multiple GROUP BY Fields

PsoftQL Request

Generated SQL

Response

Example 4: Multiple Aggregate Functions

PsoftQL Request

Generated SQL

Response (first 4 of many groups, trimmed for readability via rowLimit: 5)

Example 5: No GROUP BY (Total Aggregation)

PsoftQL Request

Generated SQL

Response

Example 6: COUNT DISTINCT

PsoftQL Request

Generated SQL

Response (first 3 groups via rowLimit: 3)

Example 7: With Effective Date Logic

PsoftQL Request

Generated SQL (Oracle)

Response (first 4 of 12 institutions)

Example 8: With rowLimit (Top N)

PsoftQL Request

Generated SQL (Oracle 12c+)

Generated SQL (SQL Server)

Response

Example 9: With sqlWhereClause

PsoftQL Request

Generated SQL

Response

Example 10: Users per Role (Count from Related Table)

PsoftQL Request

Generated SQL

Response (first 5 of many groups via rowLimit: 5)

Implementation Notes

SQL Generation Pattern

Field Prefix Handling

Validation Checks

Response (first 4 of many groups, trimmed for readability via `rowLimit: 5`)

Response (first 3 groups via `rowLimit: 3`)

Response (first 5 of many groups via `rowLimit: 5`)