title: group twice in one query
body: <p>I use below code but doesn't return what I expect, </p>
  
  <p>the table relationship,<br>
  each <code>gallery</code> is include multiple <code>media</code> and each media is include multiple <code>media_user_action</code>. 
  I want to count each <code>gallery</code> how many <code>media_user_action</code> and order by this count </p>
  
  <pre><code>rows: [
   {
     "id": 1
   },
   {
     "id": 2
   }
  ]
  </code></pre>
  
  <p>and this query will return duplicate gallery rows something like </p>
  
  <pre><code>rows: [
   {
     "id": 1
   },
   {
     "id": 1
   },
   {
     "id": 2
   }
   ...
  ]
  </code></pre>
  
  <p>I think because in the <code>LEFT JOIN</code> subquery select <code>media_user_action</code> rows only group by <code>media_id</code>, 
  need to group by <code>gallery_id</code> also ? </p>
  
  <pre><code>SELECT
   g.*
   FROM gallery g 
     LEFT JOIN gallery_media gm ON gm.gallery_id = g.id
     LEFT JOIN (
       SELECT 
         media_id, 
         COUNT(*) as mua_count
         FROM media_user_action
         WHERE type = 0
         GROUP BY media_id
     ) mua ON mua.media_id = gm.media_id
     ORDER BY g.id desc NULLS LAST OFFSET $1 LIMIT $2
  </code></pre>
  
  <p>table</p>
  
  <pre><code>gallery
  id |
  1  |
  2  |
  
  gallery_media
  id | gallery_id fk gallery.id | media_id fk media.id
  1  | 1                        | 1
  2  | 1                        | 2
  3  | 2                        | 3
  ....
  
  media_user_action
  id | media_id fk media.id | user_id | type
  1  | 1                    | 1       | 0
  2  | 1                    | 2       | 0
  3  | 3                    | 1       | 0
  ...
  
  media
  id | 
  1  |
  2  |
  3  |
  </code></pre>
  
  <p><strong>UPDATE</strong><br>
  There's more other table I need to select, this is a part in a function like this <a href="https://jsfiddle.net/g8wtqqqa/1/" rel="nofollow">https://jsfiddle.net/g8wtqqqa/1/</a>  when user input option then build query.  </p>
  
  <p>So I correct my question I need to find a way if user want to count <code>media_user_action</code> order by it, I wanna know how to put these in a subquery possible not change any other code</p>
  
  <p><br><br><br>
  Base on below @trincot answer I update code, only add <code>media_count</code> on top change a little bit and put those in sub query. is what I want,<br>
  now they are group by gallery.id, but sort media_count desc and asc are same result not working I can't find why?</p>
  
  <pre><code>SELECT
   g.*,
   row_to_json(gi.*) as gallery_information,
   row_to_json(gl.*) as gallery_limit,
   media_count
   FROM gallery g 
   LEFT JOIN gallery_information gi ON gi.gallery_id = g.id
   LEFT JOIN gallery_limit gl ON gl.gallery_id = g.id
   LEFT JOIN "user" u ON u.id = g.create_by_user_id
   LEFT JOIN category_gallery cg ON cg.gallery_id = g.id
   LEFT JOIN category c ON c.id = cg.category_id 
     LEFT JOIN (
       SELECT 
         gm.gallery_id,
         COUNT(DISTINCT mua.media_id) media_count
         FROM gallery_media gm
         INNER JOIN media_user_action mua
           ON mua.media_id = gm.media_id AND mua.type = 0
         GROUP BY gm.gallery_id
     ) gm ON gm.gallery_id = g.id
     ORDER BY gm.media_count asc NULLS LAST OFFSET $1 LIMIT $2
  </code></pre>