Re: Using COUNT() with columns that don't use COUNT() when the table is join fails

[Maryann Xue]

Thank you very much for your answer, Michael! Yes, what Cheyenne tried to
use was simply not the right grammar.


Thanks,
Maryann

On Mon, Sep 19, 2016 at 10:47 AM, Michael McAllister <
mmcallis...@homeaway.com> wrote:

> This is really an ANSI SQL question. If you use an aggregate function,
> then you need to specify what columns to group by. Any columns not being
> referenced in the aggregate function(s) need to be in the GROUP BY
> statement.
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallis...@homeaway.com | C: 512.423.7447 | skype: michael.mcallister.ha
>  | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited.  If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Cheyenne Forbes 
> *Reply-To: *"user@phoenix.apache.org" 
> *Date: *Monday, September 19, 2016 at 10:50 AM
> *To: *"user@phoenix.apache.org" 
> *Subject: *Re: Using COUNT() with columns that don't use COUNT() when the
> table is join fails
>
>
>
> I was wondering because it seems extra wordy
>

Re: Using COUNT() with columns that don't use COUNT() when the table is join fails

[Michael McAllister]

This is really an ANSI SQL question. If you use an aggregate function, then you 
need to specify what columns to group by. Any columns not being referenced in 
the aggregate function(s) need to be in the GROUP BY statement.

Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallis...@homeaway.com | C: 512.423.7447 | 
skype: michael.mcallister.ha | webex: 
https://h.a/mikewebex
[cid:image001.png@01D21273.F8F1C960]
This electronic communication (including any attachment) is confidential.  If 
you are not an intended recipient of this communication, please be advised that 
any disclosure, dissemination, distribution, copying or other use of this 
communication or any attachment is strictly prohibited.  If you have received 
this communication in error, please notify the sender immediately by reply 
e-mail and promptly destroy all electronic and printed copies of this 
communication and any attachment.

From: Cheyenne Forbes 
Reply-To: "user@phoenix.apache.org" 
Date: Monday, September 19, 2016 at 10:50 AM
To: "user@phoenix.apache.org" 
Subject: Re: Using COUNT() with columns that don't use COUNT() when the table 
is join fails

I was wondering because it seems extra wordy

Re: Using COUNT() with columns that don't use COUNT() when the table is join fails

[Cheyenne Forbes]

I was wondering because it seems extra wordy

Re: Using COUNT() with columns that don't use COUNT() when the table is join fails

[Steve Terrell]

I'm not an expert in traditional SQL or in Phoenix SQL, but my best guess
is "probably not".

But I'm curious as to why you would like to avoid the group by or the list
of columns.  I know it looks very wordy, but are there any technical
reasons?  In my experience SQL is hard to read by human eyes by nature, so
I just get used to it.

On Mon, Sep 19, 2016 at 10:06 AM, Cheyenne Forbes <
cheyenne.osanu.for...@gmail.com> wrote:

> Hi steve,
>
> Thank you, it works when I add group by, can I avoid using group by or
> avoid adding all my columns to the group by if I have 10 columns being
> queried?
>

Re: Using COUNT() with columns that don't use COUNT() when the table is join fails

[Steve Terrell]

Hi!  I think you need something like
group by u.first_name
on the end.  Best guess.  :)

On Sun, Sep 18, 2016 at 11:03 PM, Cheyenne Forbes <
cheyenne.osanu.for...@gmail.com> wrote:

> this query fails:
>
> SELECT COUNT(fr.friend_1), u.first_name
>>
>> FROM users AS u
>>
>> LEFT JOIN friends AS fr ON u.id = fr.friend_2
>>
>>
> with:
>
> SQLException: ERROR 1018 (42Y27): Aggregate may not contain columns not in
>> GROUP BY. U.FIRST_NAME
>>
>
> TABLES:
>
> users table with these columns ( id, first_name, last_name )
>
>
> friends table with these columns ( friend_1, friend_2 )
>
>
>