CODEWITHSUNDEEP
sqlgoogle-bigquery
user71812
user71812

Reputation: 457

Bigquery Error code: Window ORDER BY expression references column start_date which is neither grouped nor aggregated at

I am using BigQuery for SQL and I can't figure out why there is an error message that comes like this:

Window ORDER BY expression references column start_date which is neither grouped nor aggregated at [4:73]

Here is my code:

SELECT EXTRACT(WEEK FROM start_date) as week, count(start_date) as count,
RANK() OVER (PARTITION BY start_station_name ORDER BY EXTRACT(WEEK FROM start_date))
from `bigquery-public-data.london_bicycles.cycle_hire`
GROUP BY EXTRACT(WEEK FROM start_date), start_station_name)

I thought I have grouped the week below, as seen in the last line. So what can cause this error message to keep popping up?

Upvotes: 0

Views: 3565

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

This is a parsing error in BigQuery, which you can work around with an aggregation function. Your query has another issue, which is the start_station_name. 

SELECT EXTRACT(WEEK FROM start_date) as week, start_station_name, count(start_date) as count,
       RANK() OVER (PARTITION BY start_station_name ORDER BY MIN(EXTRACT(WEEK FROM start_date)))
from `bigquery-public-data.london_bicycles.cycle_hire`
GROUP BY 1, 2;

The MIN() really serves no purpose other than lettering BigQuery parse the query. Because the expression is part of the GROUP BY, there is only one value for the MIN() to consider.

This is a bug in the BigQuery parsing, because it does not recognize that the expression is the same as the expression in the GROUP BY. Happily, it is easy to work around.

Upvotes: 4

indrajeet0510
indrajeet0510

Reputation: 78

In query you need to make sure that you have to put ORDER BY only on those values which you are selecting.

With your query, the problem is you are doing ORDER BY EXTRACT(WEEK from start_date). Rather than doing this you should write ORDER BY week because you are selecting week already

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32031

try like below using cte

  with cte as
    (
    SELECT *, EXTRACT(WEEK FROM start_date) as week

    from `bigquery-public-data.london_bicycles.cycle_hire`

    ) select week,count(start_date) as count,
     RANK() OVER (PARTITION BY start_station_name ORDER BY week)
     from cte group by week,start_station_name

Upvotes: 1

Related Questions