From: "Dan Langille" <dan@langille.org>
To: "Chad Thompson" <chad@weblinkservices.com>
Date: Fri, 21 Feb 2003 15:45:45 -0500
MIME-Version: 1.0
Subject: Re: 7.3 "group by" issue
Cc: pgsql-sql@postgresql.org
Message-ID: <3E5649A9.26494.1F008FD6@localhost>
In-reply-to: <050201c2d9e8$23acc340$32021aac@chad>
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body

On 21 Feb 2003 at 13:30, Chad Thompson wrote:

> 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

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


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

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

My question: why should it not work?  It's referring to the same 
column as the previous two examples which do work.
-- 
Dan Langille : http://www.langille.org/