Big Query Order By Grouped Field

Question

I have a query that groups by date which works fine.

SELECT EXTRACT(date FROM DATETIME(timestamp, 'US/Eastern')) date, SUM(users) total_users FROM `mydataset.mytable` 
GROUP BY EXTRACT(date FROM DATETIME(timestamp, 'US/Eastern'))

but when I try to order by date:

SELECT EXTRACT(date FROM DATETIME(timestamp, 'US/Eastern')) date, SUM(users) total_users FROM `mydataset.mytable` 
GROUP BY EXTRACT(date FROM DATETIME(timestamp, 'US/Eastern'))
ORDER BY EXTRACT(date FROM DATETIME(timestamp, 'US/Eastern'));

I get the following error:

SELECT list expression references column timestamp which is neither grouped nor aggregated at [1:35]

The timestamp column is clearly part of the group by and even stranger still is that it works without the ORDER BY clause... What's going on here?

Answer 1

#standardSQL
SELECT 
  EXTRACT(DATE FROM DATETIME(timestamp, 'US/Eastern')) date, 
  SUM(users) total_users 
FROM `mydataset.mytable` 
GROUP BY 1
ORDER BY 1 

Answer 2

You can try subselect:

#standardSQL
SELECT
  date,
  total_users
FROM (
  SELECT
    EXTRACT(date FROM DATETIME(timestamp,'US/Eastern')) date,
    SUM(users) total_users
  FROM
   `mydataset.mytable`
  GROUP BY EXTRACT(date FROM DATETIME(timestamp, 'US/Eastern'))
  )
ORDER BY
   date