pgSQL query error

Question

i tried using this query:

"SELECT * FROM guests WHERE event_id=".$id." GROUP BY member_id;"

and I'm getting this error:

ERROR: column "guests.id" must appear in the GROUP BY clause or be used in an aggregate function

can anyone explain how i can work around this?

Answer 1

You can't Group By without letting the Select know what to take, and how to group.
Try

SELECT guests.member_id FROM guests WHERE event_id=".$id." GROUP BY member_id;

IF you need to get more info from this table about the guests, you'll need to add it to the Group By.

Plus, it seems like your select should actually be

SELECT guests.id FROM guests WHERE event_id=".$id." GROUP BY id;

Answer 2

Each of the columns used in a group by query needs to be specifically called out (ie, don't do SELECT * FROM ...), as you need to use them in some sort of aggregate function (min/max/sum/avg/count/etc) or be part of the group by clause.

For example:

SELECT instrument, detector, min(date_obs), max(date_obs)
FROM observations
WHERE observatory='SOHO'
GROUP BY instrument, detector;