HIVE SQL [Error 10025]: Expression not in GROUP BY key Here is my SQL statement:

my_table includes 10+ columns (e.g, day, ip_address, user, request, etc), including strings and numbers. I want to GROUP by &amp; HAVING based on column 'ip_address', if more than 20 records.

SELECT day, ip_address, user, request 
FROM my_table
WHERE DAY = current_date()
GROUP BY ip_address
HAVING count(client_ip)&gt;20


I got this error message

Error while compiling statement: FAILED: SemanticException [Error 10025]: Expression not in GROUP BY key xxx


But I need to keep all columns.
As long as one ip address has more than 20 rows, all these rows will be returned. If less then 20, do not return.


WITH cte AS ( SELECT *, COUNT(ip_address) OVER (PARTITION BY ip_address) cnt
              FROM my_table
              WHERE DAY = current_date() )
SELECT *
FROM cte
WHERE cnt &gt; 20;