title: Where to insert a GROUP BY in a complex PostgreSQL query which aggregate two tables
body: <p>In the below PSQL query I need to add a <code>GROUP BY member.id</code> but I have difficulties to where to put it as always saying syntax error on the <code>GROUP BY</code>.
  The query is aggregating 2 tables and update message excluding where we have a <code>USER</code> type. The script complains about the missing <code>GROUP BY member.id</code> but have no idea at the moment where to put it and need ideas.</p>
  <pre><code>update
      message m
  set
      status = 'DEFAULT'
  where
      status = 'PENDING'
      and conversation_id = '1'
      and not exists (
      select
          1
      from
          &quot;member&quot;
      having
          id = m.member_id
          and origin_type = 'USER')
      and m.updated_at &lt; (
      select
          max(m.updated_at)
      from
          message
      having
          conversation_id = m.conversation_id
          and origin_type = 'USER')
  </code></pre>
  <p>The tables where I'm doing this as screenshot</p>
  <p>Message</p>
  <p><a href="https://i.stack.imgur.com/on9Ub.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/on9Ub.png" alt="Message table" /></a></p>
  <p>Member</p>
  <p><a href="https://i.stack.imgur.com/E3ct1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/E3ct1.png" alt="Member" /></a></p>