01 Jul 20 · Ryan-Dallas · #bigquery ·   Bookmark   ×

Solved: Bigquery Aggregations of aggregations are not allowed

When you are using an aggregate function in Biquery, and wrap it inside another aggregate function, Bigquery throws the below error message:

Example Query:

SELECT name, count(min(year)) FROM `project.Dataset.Names` group by name;
Error message: Aggregations of aggregations are not allowed at [1:8]

Solution:

You can have only one aggregate function per group by statement. You will need to rewrite the query, add another select clause on top and do the second aggregate function there, Checkout the below example:

SELECT name, COUNT(year) FROM
(
SELECT name, min(year) as year FROM `naveens-projects.Dataset2.Names` group by name
)
GROUP BY name;

Ryan-Dallas

posted on 01 Jul 20

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