You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Stefán Baxter <st...@activitystream.com> on 2016/03/04 20:34:34 UTC

Is using an aggregate value in a where clause not supported?

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

Re: Is using an aggregate value in a where clause not supported?

Posted by Stefán Baxter <st...@activitystream.com>.
Yes, this works.

Thank you,
 -Stefán

On Fri, Mar 4, 2016 at 8:55 PM, Jinfeng Ni <ji...@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
> <st...@activitystream.com> wrote:
> > Having fails as well
> >
> > On Fri, Mar 4, 2016 at 8:00 PM, Bob Rumsby <br...@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 <
> stefan@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 <br...@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 <
> >> > stefan@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
> >> > > >
> >> > >
> >> >
> >>
>

Re: Is using an aggregate value in a where clause not supported?

Posted by Jinfeng Ni <ji...@gmail.com>.
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
<st...@activitystream.com> wrote:
> Having fails as well
>
> On Fri, Mar 4, 2016 at 8:00 PM, Bob Rumsby <br...@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 <st...@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 <br...@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 <
>> > stefan@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
>> > > >
>> > >
>> >
>>

Re: Is using an aggregate value in a where clause not supported?

Posted by Stefán Baxter <st...@activitystream.com>.
Having fails as well

On Fri, Mar 4, 2016 at 8:00 PM, Bob Rumsby <br...@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 <st...@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 <br...@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 <
> > stefan@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
> > > >
> > >
> >
>

Re: Is using an aggregate value in a where clause not supported?

Posted by Bob Rumsby <br...@maprtech.com>.
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 <st...@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 <br...@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 <
> stefan@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
> > >
> >
>

Re: Is using an aggregate value in a where clause not supported?

Posted by Stefán Baxter <st...@activitystream.com>.
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 <br...@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 <st...@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
> >
>

Re: Is using an aggregate value in a where clause not supported?

Posted by Bob Rumsby <br...@maprtech.com>.
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 <st...@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
>