---
title: "WQL AVG() Integer Rounding and CASE / DECODE Alternatives"
description: "How to get decimal precision when WQL AVG() rounds to whole numbers without ROUND() or CAST(), and how to express CASE WHEN / DECODE logic in WQL."
url: https://kiweely.com/guides/wql-avg-integer-rounding-conditional-logic-without-case
---

# Why WQL AVG() Rounds to Whole Numbers and How to Handle Conditional Logic Without CASE

WQL does not support ROUND(), CAST(), CASE WHEN, or DECODE(), and its built-in AVG() function rounds integer fields to whole numbers. Learn how to compute exact decimal averages with SUM() and COUNT() and how to implement conditional boolean logic in WQL.

-   **Checked** against Workday on November 6
-   Applies to **Workday Query Language**
-   3 min read
-   Published November 6
-   By Kiweely

## Why `AVG(field)` in WQL Rounds to Whole Numbers

When aggregating numeric ratings, scores, or counts across grouped records in Workday Query Language (WQL), developers often use `AVG(field)` expecting a floating-point average (such as `"4.25"`):

sql

```
SELECT
  workerIDs,
  nomination,
  nominationType,
  COUNT(workdayID) AS voteCount,
  AVG(score) AS voteAvg
FROM employeeOfTheMonthApp_nstjht_nominationVotes
WHERE completionDate >= '2026-02-01'
  AND completionDate <= '2026-02-28'
GROUP BY nomination, workerIDs, nominationType
```

When `score` is defined on the business object as an integer numeric field (or has scale `0`), WQL’s `AVG(score)` inherits the scale of the underlying field and returns **whole numbers only** (`"4"` instead of `"4.25"`).

Unlike SQL, WQL’s `SELECT` clause supports only six aggregation functions—`AVG(field)`, `COUNT()`, `COUNT(DISTINCT field)`, `MIN(field)`, `MAX(field)`, and `SUM(field)`—and **does not support `ROUND()`, `CAST()`, `CONVERT()`, or inline arithmetic expressions** such as `AVG(score * 1.0)`.

### How to Compute Decimal Averages in WQL + PMD or Orchestrate

To preserve decimal precision without changing the schema of an existing integer field, replace `AVG(score)` in your WQL `SELECT` clause with `SUM(score)` and `COUNT(workdayID)`:

sql

```
SELECT
  workerIDs,
  nomination,
  nominationType,
  COUNT(workdayID) AS voteCount,
  SUM(score) AS scoreSum
FROM employeeOfTheMonthApp_nstjht_nominationVotes
WHERE completionDate >= '2026-02-01'
  AND completionDate <= '2026-02-28'
GROUP BY nomination, workerIDs, nominationType
```

Then compute the floating-point average and round to two decimal places in your PMD script or Orchestration expression:

javascript

```
var rows = getNominationVotes.data ?? [];
for (var row : rows) {
  var count = number:convertStringToDecimal(row.voteCount ?? "0");
  var sum = number:convertStringToDecimal(row.scoreSum ?? "0");
  if (count > 0) {
    var rawAvg = sum / count;
    // Format to 2 decimal places
    row.voteAvg = number:format(rawAvg, "#,##0.00");
  } else {
    row.voteAvg = "0.00";
  }
}
```

If you are designing a new Extend Model Business Object field where you want `AVG(score)` to return decimals directly from WQL without post-processing, configure the `number` field in your `.bo` model with `fractionDigits: 2` (decimal scale) rather than an integer scale of `0`.

## How to Implement `CASE WHEN` or `DECODE` Conditional Logic in WQL

A related SQL habit that fails in WQL is attempting to write inline conditional expressions—such as `CASE WHEN field = 'X' THEN true ELSE false END` or Oracle-style `DECODE()`—inside the `SELECT` clause. WQL has no inline conditional operators in `SELECT`.

How you implement conditional output depends on whether the condition uses static/tenant-level rules or dynamic runtime parameters from an orchestration or PMD page.

### Pattern 1: Evaluate Expression or True/False Calculated Field (`cf_...`) for Static Rules

When the condition depends only on fields of the business object (or relative dates like `Today`), build a Workday Calculated Field and select its `cf_...` alias in WQL:

-   **Single Boolean Check:** Create a **True/False Condition** calculated field (for example, `cf_isEligible`) and include `SELECT cf_isEligible` in your WQL query. WQL returns `true` or `false` directly in the JSON payload.
-   **Multi-Branch `CASE WHEN` / `DECODE` Logic:** Create an **Evaluate Expression** calculated field on the business object, add each condition branch and its corresponding return value, set the default value, and select `cf_evaluatedTier` in your WQL query.

### Pattern 2: Push Dynamic Runtime Conditions into `WHERE` and Check `total > 0`

Suppose an Orchestration or PMD page receives a dynamic `startDate` and `endDate` at runtime and needs a boolean `true`/`false` answer based on whether a worker or record meets a condition within that runtime window. You cannot pass arbitrary orchestration variables into an `Evaluate Expression` calculated field without report prompts.

Instead, push the full conditional predicate—including your runtime date range—into the WQL `WHERE` clause and evaluate whether the query matched any records:

sql

```
SELECT workdayID
FROM workerTimeBlocks
WHERE worker IN (3aa5550b7fe348b98d7b5741afc65534)
  AND reportedDate >= '2026-06-01'
  AND reportedDate <= '2026-06-30'
  AND timeBlockStatus = 'Approved'
LIMIT 1
```

In the next step of your Orchestration or PMD script, evaluate the returned `total` (or `rowCount`) directly:

javascript

```
// In Orchestrate Create Values or Branch on Conditions:
data.ExecuteWQL.response.asJSON().numberAtJsonPath("$.total") > 0

// In PMD Script:
var hasApprovedTime = (checkApprovedTime.total ?? 0) > 0;
```

When you need to classify rows into multiple buckets across a list of records fetched in a single query, select the raw comparison fields in WQL (`SELECT workdayID, status, completionDate`) and apply a ternary expression (`row.status == 'Approved' ? true : false`) in your PMD or Orchestrate transformation step.

## How to Verify in the Tenant

1.  Open **View WQL Query Result** in your tenant and execute your `GROUP BY` query with `AVG(field)`, `SUM(field)`, and `COUNT(workdayID)` side by side.
2.  Confirm that while `AVG(field)` truncates or rounds integer fields to a whole number, `SUM(field)` and `COUNT(workdayID)` return exact totals that divide cleanly to your desired decimal precision in PMD or Orchestrate.
3.  For conditional checks using dynamic date ranges, test the `WHERE ... LIMIT 1` query in **View WQL Query Result** with dates that both match and exclude a known record, verifying that `total` switches between `1` (or `> 0`) and `0`.

## Sources checked

-   [WQL Reference: SELECT](https://developer.workday.com/documentation/GUID-85db8d16-4698-4dea-bef2-23223739c8e6-enHYPHENus)
-   [WQL Reference: GROUP BY](https://developer.workday.com/documentation/GUID-f0284119-5c5b-407a-9581-672164ad9f18-enHYPHENus)
-   [WQL Reference: WHERE](https://developer.workday.com/documentation/GUID-a9f9f945-baf1-4a7d-9118-db9a8ce49265-enHYPHENus)
-   [Reference: Field Types](https://doc.workday.com/admin-guide/en-us/reporting-and-analytics/custom-reports-and-analytics/fields-and-prompts/dan1370796608038.html)

## Related guides

[All guides](https://kiweely.com/guides)

-   [### Fixing WQL Error 804 on GROUP BY and Related Business Object Aggregation Limits Grouping a large transactional data source like allActiveAndTerminatedWorkers to populate dropdowns can fail with Error 804 in implementation tenants, while selecting RBO subfields with { } alongside COUNT() or GROUP BY fails validation. Learn the exact WQL grouping rules and solutions.](https://kiweely.com/guides/wql-group-by-error-804-rbo-aggregation-limits)
-   [### Why WHERE fieldA = fieldB Throws “Invalid Where Clause” in WQL WQL is SQL-like but does not allow field-to-field comparisons such as WHERE totalApprovalLevel = currentApprovalLevel in the WHERE clause. Learn how WQL evaluates WHERE operands, how to use a True/False Calculated Field or post-query filtering, and how to use COUNT() syntax properly.](https://kiweely.com/guides/wql-compare-two-fields-where-clause-invalid)
-   [### What is Workday Query Language (WQL), and how do you write and run a query? Workday Query Language (WQL) is Workday’s SQL-like language for reading data from data sources and fields instead of reports. You need the Workday Query Language domain in the System functional area; an external caller also needs an API client with System scope.](https://kiweely.com/guides/workday-wql)
-   [### Why does my Workday WQL query fail? The exact error messages and how to fix each one Workday has no WQL message worded ‘instance set invalid for prompt’. A rejected prompt value returns ‘Invalid Parameter Values for parameter’ or ‘does not meet the restrictions defined for this field’. This page quotes each common error as Workday returned it, with the fix.](https://kiweely.com/guides/wql-error-messages)

### More guides on WQL

[All 31 guides](https://kiweely.com/guides#wql)

-   [WQL 500-Instance Related Business Object Limit and Large IN (...) Filter Limits](https://kiweely.com/guides/wql-500-rbo-instances-per-row-large-in-filter-limits)
-   [Fixing WQL HTTP 400 “Parms are required for the fields or data source in the query”](https://kiweely.com/guides/wql-400-parms-are-required-for-fields-or-data-source)
-   [Custom Agent Actions that call Workday SOAP, REST and WQL](https://kiweely.com/guides/workday-custom-agent-actions-soap-rest-wql)
-   [Running WQL in Orchestrate as an integration system user: the security it needs](https://kiweely.com/guides/workday-orchestrate-wql-as-isu)
-   [WQL Cheat Sheet: Workday Query Language Syntax, Data Sources and Working Examples](https://kiweely.com/guides/workday-wql-cheat-sheet)
-   [Workday Prism Analytics vs. WQL vs. Custom Reports: Joining External and Tenant Data](https://kiweely.com/guides/workday-prism-analytics-vs-wql)
-   [Should I use RaaS, WQL or the REST API to extract data from Workday?](https://kiweely.com/guides/workday-raas-wql-rest)
-   [How do I get more than 500 rows from a Workday WQL query?](https://kiweely.com/guides/wql-more-than-500-rows)

### From the blog on WQL

[All posts](https://kiweely.com/blog)

-   [What happens after ChatGPT writes your WQL query](https://kiweely.com/blog/after-chatgpt-writes-your-wql)
-   [Answering Executive Span-of-Control and Headcount Questions Live from Workday](https://kiweely.com/blog/executive-org-span-of-control-attrition-questions-live-workday)
-   [Build vs. Buy a Workday MCP Server: OAuth 2.1, SOAP, WQL, and Twice-Yearly Releases](https://kiweely.com/blog/build-vs-buy-workday-mcp-server-engineering-cost)

## Let Workday check the work.

Kiweely writes WQL and builds orchestrations in your own tenant, and Workday, not Kiweely, says whether they passed.

[Start for free](https://kiweely.com/download) [See how it works](https://kiweely.com/vine/how-it-works)

## Get the next guide in your inbox.

One email a month with new guides and one Extend tip. Nothing else.
