From: Chad Thompson <chad@weblinkservices.com>
To: Dan Langille <dan@langille.org>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: 7.3 "group by" issue
Date: Fri, 21 Feb 2003 13:30:56 -0700
Message-ID: <050201c2d9e8$23acc340$32021aac@chad> (raw)
References: <3E564446.13428.1EEB8204@localhost>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: chad@weblinkservices.com, dan@langille.org
Subject: Re: 7.3 "group by" issue
In-Reply-To: <050201c2d9e8$23acc340$32021aac@chad>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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