The error you're encountering in Google Cloud BigQuery, "Grouping by expressions of type STRUCT is not allowed," suggests that one or more of the columns you're trying to group by (date, admin_level_1, tests_total, tests_increase) is of type STRUCT. BigQuery does not allow grouping by STRUCT type columns directly.
However, your query doesn't seem to be using STRUCT types directly in the GROUP BY clause. The issue might be elsewhere. Let's try to refactor your query to see if we can resolve the issue. Also, I noticed that your final SELECT statement is trying to select from lep_find, which doesn't include the total_count column you calculated in the Total subquery. You might want to join these two subqueries or adjust your final SELECT to include the necessary columns.
Here's a revised version of your query:
WITH lep_find AS (
SELECT date, admin_level_1, tests_total, tests_increase
FROM `YourDB`
GROUP BY date, admin_level_1, tests_total, tests_increase
LIMIT 1000
), Total AS (
SELECT tests_total, tests_increase, SUM(tests_total + tests_increase) AS total_count
FROM lep_find
GROUP BY tests_total, tests_increase
)
SELECT lf.date, lf.admin_level_1, lf.tests_total, lf.tests_increase, t.total_count
FROM lep_find lf
JOIN Total t ON lf.tests_total = t.tests_total AND lf.tests_increase = t.tests_increase;
In this revised query:
- I've maintained your initial
lep_find subquery.
- In the
Total subquery, I've added a GROUP BY clause to ensure proper aggregation.
- The final
SELECT joins lep_find with Total to include the total_count in your result set.
LinkedIn
Twitter
