Count where or having clause does not work as expected

[Stefán Baxter]

Hi,

I have data that can be reduced to this:

   - {"client_ip":{"country_code":"US"}}
   - {"client_ip":{"country_code":"US"}}
   - {"client_ip":{"country_code":"US"}}
   - {"client_ip":{"country_code":"GB"}}
   - {"client_ip":{"country_code":"US"}}

This works fine:

select p.client_ip.country_code country_code, count(*) event_count from
dfs.tmp.`/events` as p group by p.client_ip.country_code;


+---------+--------------+
| country_code  | event_count  |
+---------+--------------+
| US      | 21516        |
| GB      | 323594       |
+---------+--------------+


This simple where clause returns nothing:

select p.client_ip.country_code country_code, count(*) event_count from
dfs.tmp.`/events` as p where event_count > 30000 group by
p.client_ip.country_code;
+---------------+--------------+
| country_code  | event_count  |
+---------------+--------------+
+---------------+--------------+


Using a having clause (v1):

select p.client_ip.country_code country_code, count(*) event_count from
dfs.tmp.`/events` as p group by p.client_ip.country_code having event_count
> 30000;

Error: PARSE ERROR: From line 1, column 169 to line 1, column 179:
Expression 'event_count' is not being grouped


Using a having clause (v2):

select p.client_ip.country_code country_code, count(*) event_count from
dfs.tmp.`/events` as p group by p.client_ip.country_code having count(*) >
30000;
+---------------+--------------+
| country_code  | event_count  |
+---------------+--------------+
| GB            | 323594       |
| null*          | 566667       |
+---------------+--------------+


* there are no null values in the database and this looks to be the "total
for the rest"


Does anyone know how this can be made to work?

Regards,
 -Stefan