Solved: Bigquery Column xyz contains an analytic function, which is not allowed in GROUP BY

When you are using Window functions in Bigquery, and if you try to perform a group by on the window function generated column. Bigquery will throw the below error:

Column x contains an analytic function, which is not allowed in GROUP BY
\n\n

Example:

  SELECT\n    DISTINCT UserId,\n    COUNT(date) OVER (PARTITION BY UserId) AS Days\n  FROM\n    `project.Dataset.Users`\n  GROUP BY\n    Days

Solution:

GROUP BY cannot be applied to the variable whose value will be determined by a window function. The solution to this issue is to move the GROUP BY to the outer query. The outer query can do the grouping required.

\n\n

Like shown below:

SELECT
COUNT(UserId) AS Users,
Days
FROM (
SELECT
DISTINCT UserId,
COUNT(date) OVER (PARTITION BY UserId) AS Days
FROM
`project.Dataset.Users`
)
GROUP BY Days

victor

posted on

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds