make that "Count in where or having clause does not work as expected"
On Thu, Jul 30, 2015 at 2:14 PM, Stefán Baxter <ste...@activitystream.com>
wrote:
> 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
>
