Re: 7.3 "group by" issue

> 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