pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
group by
5+ messages / 5 participants
[nested] [flat]

* group by
@ 1999-12-05 03:49 tjk@tksoft.com <tjk@tksoft.com>
  1999-12-05 05:47 ` Re: [SQL] group by Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: tjk@tksoft.com @ 1999-12-05 03:49 UTC (permalink / raw)
  To: pgsql-sql

Is there something amiss with the "GROUP BY" clause?

I never had any problems with it, but I was just doing 
a query trying to eliminate duplicates from a list
using GROUP BY on one of the fields, and I got
an "Illegal use of aggregates or non-group column in target list"
error.

E.g. "select email,username from emails group by email" 
     produces the error.
     "select email from emails group by email" 
      works, and so does 
      "select distinct on email email,username from emails"

My PG version is 6.5.3.

"distinct on columnname" works and does the job, but
I am curious if I am brainfarting or if this is a 
real change.

Thanks,

Troy

 




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] group by
  1999-12-05 03:49 group by tjk@tksoft.com <tjk@tksoft.com>
@ 1999-12-05 05:47 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 1999-12-05 05:47 UTC (permalink / raw)
  To: tjk@tksoft.com <tjk@tksoft.com>; +Cc: pgsql-sql

"tjk@tksoft.com" <tjk@tksoft.com> writes:
> Is there something amiss with the "GROUP BY" clause?

No.  Not in this example, anyway.

> E.g. "select email,username from emails group by email" 
>      produces
>      "Illegal use of aggregates or non-group column in target list"

As it should.  Which username are you expecting to retrieve from
each group?  The query is not well-defined.  You could do something
like

	select email,min(username) from emails group by email

and get a well-defined result.  Basically, if you use GROUP BY
then any non-grouped columns can only appear as the arguments of
aggregate functions.

Postgres versions before 6.5 were rather lax about catching this
class of error, but 6.5 is more careful about it.

I'm not very happy with the wording of the error message; it's both
vague and confusing.  But I'm not sure how to do better, either.
The routine that checks for this is looking at a very low-level
condition that could cover a multitude of sins, and I don't see any
easy way to deliver a more-specific error message that wouldn't be
wrong (or at least misleading) as often as not :-(

>       ... and so does 
>       "select distinct on email email,username from emails"

SELECT DISTINCT ON is a non-standard abomination that does not
give predictable results.  It's basically the same problem: if
you take only one tuple out of each group with the same value
of "email", which tuple do you get?  The results are totally
dependent on unspecified implementation choices.  I've suggested
several times that we ought to remove SELECT DISTINCT ON, and unless
there's a real groundswell of discontent I'm probably going to
rip it out of 7.0...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* GROUP by
@ 2000-07-05 11:14 Antti Linno <alligator@all.ee>
  2000-07-05 11:50 ` Re: GROUP by Jerome Alet <alet@unice.fr>
  2000-07-05 11:52 ` Re: GROUP by Jesus Aneiros <aneiros@jagua.cfg.sld.cu>
  0 siblings, 2 replies; 5+ messages in thread

From: Antti Linno @ 2000-07-05 11:14 UTC (permalink / raw)
  To: pgsql-sql

I have table job, and I want to select data from it, grouping by
workgroups. When I use 
 select id,name from job where workgroup='top leaders' AND ...  group by
workgroup;
When I want to group, I get 
ERROR:  Illegal use of aggregates or non-group column in target list
I'm confused, why does query work only with sum,min etc. alone in
select.

Antti





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: GROUP by
  2000-07-05 11:14 GROUP by Antti Linno <alligator@all.ee>
@ 2000-07-05 11:50 ` Jerome Alet <alet@unice.fr>
  1 sibling, 0 replies; 5+ messages in thread

From: Jerome Alet @ 2000-07-05 11:50 UTC (permalink / raw)
  To: Antti Linno <alligator@all.ee>; +Cc: pgsql-sql

On Wed, 5 Jul 2000, Antti Linno wrote:

> I have table job, and I want to select data from it, grouping by
> workgroups. When I use 
>  select id,name from job where workgroup='top leaders' AND ...  group by
> workgroup;

this query is probably incorrect: because of the "where workgroup=" the
"group by workgroup" can't do anything.

bye,

Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome
Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30 
28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: GROUP by
  2000-07-05 11:14 GROUP by Antti Linno <alligator@all.ee>
@ 2000-07-05 11:52 ` Jesus Aneiros <aneiros@jagua.cfg.sld.cu>
  1 sibling, 0 replies; 5+ messages in thread

From: Jesus Aneiros @ 2000-07-05 11:52 UTC (permalink / raw)
  To: Antti Linno <alligator@all.ee>; +Cc: pgsql-sql

You can use group by without having an aggregate operator but if you use
an aggregate you have to put the group by column int the select.

By the way what is after the AND? I hope it is not another workgroup
condition because it doesn't have much sense, I think. If you are
filtering different types of workgroup you should use OR.

Jesus.

On Wed, 5 Jul 2000, Antti Linno wrote:

> I have table job, and I want to select data from it, grouping by
> workgroups. When I use 
>  select id,name from job where workgroup='top leaders' AND ...  group by
> workgroup;
> When I want to group, I get 
> ERROR:  Illegal use of aggregates or non-group column in target list
> I'm confused, why does query work only with sum,min etc. alone in
> select.
> 
> Antti
> 
> 




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2000-07-05 11:52 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-05 03:49 group by tjk@tksoft.com <tjk@tksoft.com>
1999-12-05 05:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-05 11:14 GROUP by Antti Linno <alligator@all.ee>
2000-07-05 11:50 ` Re: GROUP by Jerome Alet <alet@unice.fr>
2000-07-05 11:52 ` Re: GROUP by Jesus Aneiros <aneiros@jagua.cfg.sld.cu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox