X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id DC33647601E
	for <pgsql-sql@postgresql.org>; Fri, 21 Feb 2003 16:05:54 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 72287-02 for <pgsql-sql@postgresql.org>;
	Fri, 21 Feb 2003 16:05:44 -0500 (EST)
Received: from utahisp.com (cyber-wire.com [66.239.12.3])
	by postgresql.org (Postfix) with ESMTP id C0F43475DC0
	for <pgsql-sql@postgresql.org>; Fri, 21 Feb 2003 15:34:03 -0500 (EST)
Received: from chad [63.230.8.76] by utahisp.com
	(SMTPD32-7.14) id ACE13040348; Fri, 21 Feb 2003 13:32:33 -0700
Message-ID: <050201c2d9e8$23acc340$32021aac@chad>
From: "Chad Thompson" <chad@weblinkservices.com>
To: "Dan Langille" <dan@langille.org>, "pgsql-sql" <pgsql-sql@postgresql.org>
References: <3E564446.13428.1EEB8204@localhost>
Subject: Re: 7.3 "group by" issue
Date: Fri, 21 Feb 2003 13:30:56 -0700
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2720.3000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Declude-Sender: chad@weblinkservices.com [63.230.8.76]
X-Note: This E-mail was scanned by Declude JunkMail (www.declude.com) for
 spam.
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/488
X-Sequence-Number: 11886




> On 21 Feb 2003 at 13:00, Chad Thompson wrote:
>
>
> > > On 21 Feb 2003 at 19:18, Gaetano Mendola wrote:
> > >
> > > > > Hi folks,
> > > > >
> > > > > This query:
> > > > >
> > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id)
> > > > >     FROM watch_list JOIN watch_list_element
> > > > >         ON watch_list.id      = watch_list_element.watch_list_id
> > > > >        AND watch_list.user_id = 1
> > > > >   GROUP BY watch_list_element.element_id
> > > >
> > > > Try:
> > > >
> > > > SELECT element_id as wle_element_id, COUNT(watch_list_id)
> > > >      FROM watch_list JOIN watch_list_element
> > > >          ON watch_list.id      = watch_list_element.watch_list_id
> > > > WHERE
> > > >     watch_list.user_id = 1
> > > >    GROUP BY watch_list_element.element_id
> > >
> > > ERROR:  Attribute unnamed_join.element_id must be GROUPed or used in
> > > an aggregate function
> > >
> >
> > I think that the wrong problem was solved here.  Items in the order by
> > clause must be in the target list.
> >
> > heres what it says in the docs
> > *The ORDER BY clause specifies the sort order:
> >
> > *SELECT select_list
> > *     FROM table_expression
> > *     ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...]
> > *column1, etc., refer to select list columns. These can be either the
output
> > name of a column (see Section 4.3.2) or the number of a column. Some
> > examples:
> >
> > Note that "column1, etc., refer to select list"
>
> I don't see how ORDER BY enters into this situation.  It's not used.
> What are you saying?
> --

The same applies to group by... Sorry for the confusion.

If the column is not in the select section of the statement, it cant group
by it.
Try this.

SELECT element_id as wle_element_id, COUNT(watch_list_id)
   FROM watch_list JOIN watch_list_element
     ON watch_list.id      = watch_list_element.watch_list_id
WHERE
   watch_list.user_id = 1
  GROUP BY wle_element_id