# Why Aren’t SQL Window Functions Allowed in GROUP BY?

*It’s true – the SQL order of operations means window functions aren’t allowed in GROUP BY. But you can use them anyway with this special trick.*

SQL window functions are one of the language’s most powerful features. However, the syntax of window functions is not easy to master. It has lots of details that can cause beginners to stumble. One of the most common traps is trying to use window functions in `GROUP BY`

.

## Error: Window Functions Are Not Allowed in GROUP BY

Imagine you have a table,

, that stores the results of a mid-term test taken by students. The columns in the table are the **midterm**`name`

of the student and the number of `points`

the student got on the test. You’d like to split the students into four groups of equal size based on their test result: the top 25% are in one group, the next 25% in the second group, the third 25% in the third group, and the bottom 25% in the last group. Then you’d like to see the range of points and the number of students in each group.

Here’s a query you could write:

SELECT ntile(4) OVER (ORDER BY points), min(points), max(points), count(*) FROM midterm GROUP BY ntile(4) OVER (ORDER BY points);

The `NTILE()`

function divides students into groups and assigns each student the number (1-4) of their group. We then want to group students based on the number of their group and compute the minimum, maximum, and count for each group. However, when you run this query, you’ll get an error:

ERROR: window functions are not allowed in GROUP BY LINE 7: GROUP BY ntile(4) OVER (ORDER BY points);

## Why Window Functions Aren’t Allowed in GROUP BY

The reason why window functions are not allowed in `GROUP BY`

is the order of operations in SQL. The clauses of a SQL query are processed in a different order than they are written in the query. The full order of operation in SQL is:

`FROM`

,`JOIN`

`WHERE`

`GROUP BY`

- Aggregate functions
`HAVING`

**Window functions**`SELECT`

`DISTINCT`

`UNION`

/`INTERSECT`

/`EXCEPT`

`ORDER BY`

`OFFSET`

`LIMIT`

/`FETCH`

/`TOP`

A SQL query first figures out the tables to query from, then it applies the `WHERE`

filters, and then it performs the `GROUP BY`

operation. Then it proceeds to compute aggregate functions, `HAVING`

filters, and finally window functions. So, at the time the `GROUP BY`

is evaluated, the window functions are not yet computed!

In practice, you can only directly refer to SQL window functions in the `SELECT`

and `ORDER BY`

clauses.

The order of operations in SQL is one of the most important things to remember when you write a query with window functions. If you don’t use window functions often, it’s easy to forget this, which is why we put a reminder of the order of operations in our SQL Window Functions Cheat Sheet. Make sure to bookmark it if you use window functions every once in a while!

So, SQL doesn’t let you put window functions in a `GROUP BY`

. But there happens to be a way around this ...

## How to Use Window Functions in GROUP BY

Can we fix the query so that it returns the result we want? Yes. The solution is to **use a subquery to compute the window function** you want to use in the main query. Here’s our modified example:

SELECT quartile, min(points), max(points), count(*) FROM (SELECT ntile(4) OVER (ORDER BY points) AS quartile, points FROM midterm) groups GROUP BY quartile;

In the subquery, we use the `NTILE()`

function to assign students into groups. In the main query, we compute the statistics: the minimum, maximum, and the number of students.

Another possibility is to use a common table expression (CTE), like so:

WITH groups AS ( SELECT ntile(4) OVER (ORDER BY points) AS quartile, points FROM midterm ) SELECT quartile, min(points), max(points), count(*) FROM groups GROUP BY quartile;

The query is similar to the previous version with the subquery. However, a CTE lets us define the auxiliary query before the main query; this makes the code more readable.

## Master Window Functions with LearnSQL.com

If you want to learn window functions, we recommend our interactive Window Functions course. You’ll learn how to use window functions and how to avoid common rookie mistakes. If you’re not sure if window functions are for you, read why you should learn them or this article about our Window Functions course.

If you already know window functions, make sure to download our SQL Window Functions Cheat Sheet.