Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
                json_extract() is not an aggregation or window function, so this query should not be generating that error.  Also, count(date(order_date)) is unnecessarily complex.
– Gordon Linoff
                May 28, 2020 at 11:14
                json_extract can return a complex JSON object and you can get any position in that array, therefore, it is sort of a window function.
– Guy
                May 29, 2020 at 5:02
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?
                @MehulGupta . . . "not working" is not a helpful comment.  You need to explain why.  Does it return an error?  Does it return unexpected results?
– Gordon Linoff
                May 28, 2020 at 11:13
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.