Yes, this works. Thank you, -Stefán
On Fri, Mar 4, 2016 at 8:55 PM, Jinfeng Ni <jinfengn...@gmail.com> wrote: > Can you try this: > > select sold_to, count(*) as trans_count from dfs.asa.`/transactions` > group by sold_to > having count(*) > 70; > > This is because column alias in the SELECT clause could not be used in > WHERE clause, GroupBy, or Having clause [1] > > In your original query, the "trans_count" in where/having clause is > resolved to a regular column whose name is "trans_count" in the table. > > > [1] > http://stackoverflow.com/questions/2068682/why-cant-i-use-alias-in-a-count-column-and-reference-it-in-a-having-clause > > On Fri, Mar 4, 2016 at 12:40 PM, Stefán Baxter > <ste...@activitystream.com> wrote: > > Having fails as well > > > > On Fri, Mar 4, 2016 at 8:00 PM, Bob Rumsby <brum...@maprtech.com> wrote: > > > >> Without trying it or seeing your tables/files, I would expect this to > work: > >> > >> select sold_to, count(*) as trans_count from dfs.asa.`/transactions` > >> group by sold_to > >> having trans_count > 70; > >> > >> On Fri, Mar 4, 2016 at 11:53 AM, Stefán Baxter < > ste...@activitystream.com> > >> wrote: > >> > >> > Hi, > >> > > >> > Having adds to the trouble and claims that the field needs to be > grouped > >> > and then fails the same way if it's added to group by. > >> > > >> > I ended up wrapping this in a "with <> as ()" but that is far from > ideal. > >> > > >> > Regards, > >> > -Stefán > >> > > >> > On Fri, Mar 4, 2016 at 7:50 PM, Bob Rumsby <brum...@maprtech.com> > wrote: > >> > > >> > > Try using the HAVING clause. The WHERE clause cannot constrain the > >> > results > >> > > of aggregate functions. > >> > > http://drill.apache.org/docs/having-clause/ > >> > > > >> > > On Fri, Mar 4, 2016 at 11:34 AM, Stefán Baxter < > >> > ste...@activitystream.com> > >> > > wrote: > >> > > > >> > > > Hi, > >> > > > > >> > > > I'm using parquet+drill and the following statement works just > fine: > >> > > > > >> > > > select sold_to, count(*) as trans_count from > >> > > > dfs.asa.`/processed/venuepoint/transactions` where group by > sold_to; > >> > > > > >> > > > When addin this where clause nothing is returned: > >> > > > > >> > > > select sold_to, count(*) as trans_count from > dfs.asa.`/transactions` > >> > > where > >> > > > trans_count > 70 group by sold_to; > >> > > > > >> > > > > >> > > > Is this a known limitation or a bug? > >> > > > > >> > > > Regards, > >> > > > -Stefán > >> > > > > >> > > > >> > > >> >
