Re: 7.3 "group by" issue

Dan Langille writes:

> > 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

This works because the first select list item is mentioned in the GROUP BY
clause (using its output label, this is a PostgreSQL extension).

> Yes, that works. But so do these.
>
> SELECT watch_list_element.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

This works because the first select list item is mentioned in the GROUP BY
clause.

> 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 element_id

This works because the first select list item is mentioned in the GROUP BY
clause.

> The original situation which did not work is:
>
> SELECT watch_list_element.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 element_id

This does not work because the first select list item references a column
inside a join, which is not (necessarily) mathematically identical to the
column that arrives outside of the join and is in the GROUP BY clause.
(Think of an outer join: the column outside the join might contain added
null values. Of course you are using an inner join, but the constructs
work the same either way.)

--
Peter Eisentraut peter_e(at)gmx(dot)net