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

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.

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