# SQLCODE -120: Aggregate function not valid in context

Source: https://wndsoftware.com/mainframe-errors/sqlcode-120

### SQLCODE -120: Aggregate function not valid in context

SQLCODE -120 (SQLSTATE 42903) means the statement uses an aggregate function, such as SUM or COUNT, or an OLAP specification, such as ROW_NUMBER, in a place where Db2 doesn't allow one. The WHERE clause and the SET clause of an UPDATE are the usual places.


#### Other names for this code

Also written as `SQL -120`, `SQLCODE=-120`, `SQLCODE = -120`, `db2 -120`, `sqlstate 42903`, `SQL0120N`, `aggregate function is not valid in the context`.


### What it means

An aggregate function works on a set of rows and returns one value. It only makes sense where there is a set to work on, which means the SELECT list and HAVING clause of a query. An OLAP specification such as ROW_NUMBER() OVER or RANK() OVER is computed over the rows of the result, so it has the same kind of limit.

Db2 for z/OS returns -120 when one of these appears somewhere else. The Db2 12 codes list the forbidden places. They include a SET clause, a VALUES clause, a SET transition-variable statement in a trigger, an assignment statement, a RETURN statement, the key expression of an index, and the ON condition of a MERGE. In a WHERE clause they're allowed only inside a subquery of a HAVING clause.

The statement is rejected as a whole. With static SQL this happens at bind time. With dynamic SQL it happens on PREPARE.


### Common causes

- Comparing a row against a total in WHERE, as in `WHERE SALARY > AVG(SALARY)`.
- Filtering on ROW_NUMBER() or RANK() in WHERE to get the top rows of each group.
- An UPDATE that sets a column to an aggregate directly, as in `SET TOTAL = SUM(AMOUNT)`.
- An aggregate in the VALUES list of an INSERT.
- An aggregate in a trigger's SET transition-variable statement or in a SQL routine's assignment.


### How to find the problem

- Find the aggregate or OLAP function named in the statement, and note which clause it is in.
- For static SQL, the bind job's `DSNX200I` message gives the statement number in the DBRM.
- Check whether the value is meant to be per row or per group. That decides which rewrite you need.


### How to fix it

- Put the aggregate in a subquery. `WHERE SALARY > (SELECT AVG(SALARY) FROM EMP)` is valid because the subquery has its own SELECT list.
- In an UPDATE, set the column from a scalar subquery, such as `SET TOTAL = (SELECT SUM(AMOUNT) FROM ORDER_LINE L WHERE L.ORDER_NO = O.ORDER_NO)`.
- To filter on ROW_NUMBER() or RANK(), compute it in a common table expression or nested table expression, then filter the outer query.
- Move a group-level test into HAVING if the query is already grouped.


### What it looks like


```text
DSNT408I SQLCODE = -120, ERROR:  AN AGGREGATE FUNCTION OR OLAP SPECIFICATION IS NOT VALID IN
         THE CONTEXT IN WHICH IT WAS INVOKED
DSNT418I SQLSTATE   = 42903 SQLSTATE RETURN CODE
```


### IBM documentation

[IBM documentation: -120](https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=codes-120). Use the documentation for your installed release.

[Report a correction to WND Software](https://wndsoftware.com/contact#message).

Technical reviewer: [Albert Joseph](https://wndsoftware.com/authors/albert-joseph), WND Software.

Last reviewed 2026-10-18 by the WND Software team. Written in our own words. For the full reference, check IBM’s Db2 for z/OS documentation for your release.


### Related codes

[SQLCODE -119 Column or expression in HAVING not valid Read →](https://wndsoftware.com/mainframe-errors/sqlcode-119)
[SQLCODE -122 Column or expression in SELECT list not valid Read →](https://wndsoftware.com/mainframe-errors/sqlcode-122)
[SQLCODE -104 Illegal symbol in the statement Read →](https://wndsoftware.com/mainframe-errors/sqlcode-104)
[SQLCODE -171 Invalid argument to a function Read →](https://wndsoftware.com/mainframe-errors/sqlcode-171)

### Questions


#### Why can't I use AVG in a WHERE clause?

WHERE is tested one row at a time, before any grouping. An average needs the whole set, so put it in a subquery and compare against that.


#### How do I filter on ROW_NUMBER?

Compute it in a common table expression or nested table expression, then use WHERE on the outer query. The OLAP function can't be filtered in the same query block where it is computed.


#### Is it the same on Db2 for LUW?

Yes. SQL0120N has SQLSTATE 42903 for the same kind of misuse.

[Db2 SQLCODEs](https://wndsoftware.com/mainframe-errors/db2-sqlcodes) · [SQLCODE -119](https://wndsoftware.com/mainframe-errors/sqlcode-119) · [SQLCODE -122](https://wndsoftware.com/mainframe-errors/sqlcode-122)

[← All error codes](https://wndsoftware.com/mainframe-errors/)