Re: GROUP BY or alternative means to group

[Michael Gould <mgould@xxxxxxxxxxxxxxxxxxxx>]

Thanks that is a help. I would be nice if any key could be used as those are normally the things I would do group by's

Regards

Mike Gould




From my Samsung Android tablet on T-Mobile. The first nationwide 4G network

Bruno Wolff III <bruno@xxxxxxxx> wrote:
On Mon, Mar 12, 2012 at 16:18:05 -0400,
   Michael Gould <mgould@xxxxxxxxxxxxxxxxxxxx> wrote:
>You need to include all columns that are not aggregrative columns in the group by.  Even though that is the standard it is a pain to list all columns even if you don't need them

In later versions of postgres this is relaxed a bit. If you are grouping
by a primary key, you don't need to group by columns that are fixed
by that key. For example the following query is accepted in 9.1 as gameid
is a key for games and hence we don't need to also group by ga,es.title.

SELECT games.gameid, games.title
   FROM games, crate
     WHERE
       games.gameid = crate.gameid
       AND
       games.contact = 'BOB'
       AND
       crate.touched >= current_timestamp + '4 year ago'
   GROUP BY games.gameid
   HAVING count(1) < 30
   ORDER BY games.gameid
;