# Aggregate SQL Translation Examples

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

---

LLMS index: [llms.txt](/llms.txt)

---

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

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

### Generated SQL

```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

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

### Generated SQL

```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

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

### Generated SQL

```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

```json
{
  "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

```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

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

### Generated SQL

```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

```json
{
  "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

```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

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

### Generated SQL (Oracle)

```sql
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

```json
{
  "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+)

```sql
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)

```sql
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

```json
{
  "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

```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
```

---

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

### PsoftQL Request

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

### Generated SQL

```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

```text
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

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`)