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