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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])