SYNTAX ERROR:GROUP BY clause cannot contain aggregations or window functions

select json_extract(other_detail,'$.is_substitute_allowed') as substitute,
       count(date(order_date)) as order_date 
from prescription_metrics 
group by 1; 

I am unable to group by over the extracted value from other_detail(which can be 0 or 1 only) in AWS Athena. ‘other_detail’ is a JSON while order_date is of type date. Error:

GROUP BY clause cannot contain aggregations or window functions.

The query works fine when not using GROUP BY

Answer

A simple solution is to use a nested query:

select substitute, order_date from 
(select json_extract(other_detail,'$.is_substitute_allowed') as substitute,
       count(date(order_date)) as order_date 
from prescription_metrics)
group by 1;

BTW, did you mean to have count(distinct(order_date)) or any other date manipulation (day ?) on the order_date column?