using date of datetime in group by and order by in single SELECT versus using subquery

Question

I (suddenly?) have trouble getting this very simple query to work on Google BigQuery without using a subquery and not sure why.

The data contains a datetime column and I just want to check up on the number of rows per day. However, it keeps complaining I'm using the datetime column 'which is neither grouped or aggregated'.

SELECT date(datetime_col) as row_date, count(*) as count
FROM table1
GROUP BY date(datetime_col)
ORDER BY count DESC

Without the ORDER BY it works just fine. When I add the ORDER BY it suddenly complains the

'SELECT list expression references column 'datetime_col' which is neither grouped nor aggregated'

If I remove the count and group by and order by on the date then it does work.

Now if I use a subquery to do the date casting in there it does work:

SELECT row_date, count(row_date) as count FROM 
(SELECT date(datetime_col) as row_date FROM table1)
GROUP BY row_date
ORDER BY count DESC

So I'm wondering what is going on why the first single select query is not working and if that can be fixed without using the subquery?

Sergey Geron Sergey Geron · Accepted Answer · 2021-02-22T07:40:32

Try putting row_date into GROUP BY:

SELECT date(datetime_col) as row_date, count(*) as count
FROM table1
GROUP BY row_date
ORDER BY count DESC

using date of datetime in group by and order by in single SELECT versus using subquery

1 Answers