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@gmx.net