Last I checked group by and having clauses do not support column aliases. —Andries
> On Jul 30, 2015, at 7:19 AM, Stefán Baxter <[email protected]> wrote: > > Hi, > > That last case works as expected, sorry, this test data does have null > values for country_code. > > That means that I have a working solution but that it would be nice if v1 > (above) would work. > > Thank you, > -Stefán > > On Thu, Jul 30, 2015 at 2:15 PM, Stefán Baxter <[email protected]> > wrote: > >> 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 <[email protected]> >> 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 >>> >> >>
