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 >