Aggregate SQL Translation Examples

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

This document shows how PsoftQL aggregate requests translate to SQL queries. It is both an internal reference for implementation and a guide for callers assembling aggregate requests. Examples 4, 5, 6, and 9 include sample response bodies drawn from the integration test suite in doc-internal/test/test-pql-aggregate.http.

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

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

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

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

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.

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

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

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

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

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

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:

InputOutput
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

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