title: MySQL: Union, Count, and Group By
body: <pre><code>(
  SELECT root_tags.tag_id, root_tags.tag_name, COUNT( root_tagged.pg_id )
  FROM root_tags
  
  LEFT JOIN root_tagged ON ( root_tagged.tag_id = root_tags.tag_id )
  LEFT JOIN root_pages ON ( root_pages.pg_id =  root_tagged.pg_id )
  LEFT JOIN root_granted ON ( root_granted.pg_id =  root_tagged.pg_id )
  
  WHERE root_pages.parent_id = '5'
  AND root_granted.mem_id = '3'
  
  GROUP BY root_tags.tag_id
  ORDER BY 3 DESC
  )
  
  UNION
  (
  SELECT root_tags.tag_id, root_tags.tag_name, COUNT( root_tagged.pg_id )
  FROM root_tags
  
  LEFT JOIN root_tagged ON ( root_tagged.tag_id = root_tags.tag_id )
  LEFT JOIN root_pages ON ( root_pages.pg_id =  root_tagged.pg_id )
  
  WHERE root_pages.parent_id = '5'
  AND NOT EXISTS (
      SELECT *
      FROM root_granted
      WHERE root_granted.pg_id =  root_pages.pg_id )
  
  GROUP BY root_tags.tag_id
  ORDER BY 3 DESC
  )
  </code></pre>
  
  <p>The query above returns a result like this below,</p>
  
  <pre><code>tag_id  tag_name                COUNT(root_tags.tag_id)
  16      expert-category-c       2
  14      expert-category-a       1
  15      expert-category-b       1
  16      expert-category-c       1
  </code></pre>
  
  <p>As you can see the <code>tag_id 16</code> is repeated, how can I rewrite the query so that the <code>tag_id 16</code> has the count number of <code>3</code>, I mean I want the query supposed to return a result like this,</p>
  
  <pre><code>tag_id  tag_name                COUNT(root_tags.tag_id)
  16      expert-category-c       3
  14      expert-category-a       1
  15      expert-category-b       1
  </code></pre>
  
  <p>I tried with this query but it returns error...</p>
  
  <pre><code>(
  SELECT root_tags.tag_id, root_tags.tag_name, COUNT( root_tagged.pg_id )
  FROM root_tags
  
  LEFT JOIN root_tagged ON ( root_tagged.tag_id = root_tags.tag_id )
  LEFT JOIN root_pages ON ( root_pages.pg_id =  root_tagged.pg_id )
  LEFT JOIN root_granted ON ( root_granted.pg_id =  root_tagged.pg_id )
  
  WHERE root_pages.parent_id = '5'
  AND root_granted.mem_id = '3'
  
  )
  
  UNION
  (
  SELECT root_tags.tag_id, root_tags.tag_name, COUNT( root_tagged.pg_id )
  FROM root_tags
  
  LEFT JOIN root_tagged ON ( root_tagged.tag_id = root_tags.tag_id )
  LEFT JOIN root_pages ON ( root_pages.pg_id =  root_tagged.pg_id )
  
  WHERE root_pages.parent_id = '5'
  AND NOT EXISTS (
      SELECT *
      FROM root_granted
      WHERE root_granted.pg_id =  root_pages.pg_id )
  )
  
  GROUP BY root_tags.tag_id
  ORDER BY 3 DESC
  </code></pre>
  
  <p>Could you please let me know how to make this work?</p>
  
  <p>Thanks.</p>