MySQL: Union, Count, and Group By

Question

(
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
)

The query above returns a result like this below,

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

As you can see the tag_id 16 is repeated, how can I rewrite the query so that the tag_id 16 has the count number of 3, I mean I want the query supposed to return a result like this,

tag_id  tag_name                COUNT(root_tags.tag_id)
16      expert-category-c       3
14      expert-category-a       1
15      expert-category-b       1

I tried with this query but it returns error...

(
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

Could you please let me know how to make this work?

Thanks.

Answer 1

The better query is given further below, upon analyzing your actual query.

You can merge the two queries using UNION ALL instead of UNION (to retain duplicates), then run a GROUP BY across the entire set.

SELECT tag_id, tag_name, SUM(CountTags) as CountTags
FROM
(
SELECT root_tags.tag_id, root_tags.tag_name, COUNT( root_tagged.pg_id ) CountTags
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

UNION ALL

SELECT root_tags.tag_id, root_tags.tag_name, COUNT( root_tagged.pg_id ) CountTags
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
) SQ
GROUP BY tag_id, tag_name
ORDER BY CountTags DESC

Since your WHERE clauses filter against root_granted and root_pages, those are actually INNER JOINs. You can also use a EXISTS test to emulate the first part of the UNION, assuming you can never have more than 1 root_granted record per root_pages record.

SELECT root_tags.tag_id, root_tags.tag_name, COUNT( root_tagged.pg_id ) CountTags
FROM root_tags
INNER JOIN root_tagged ON ( root_tagged.tag_id = root_tags.tag_id )
INNER 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 )
OR EXISTS (
    SELECT *
    FROM root_granted
    WHERE root_granted.pg_id =  root_pages.pg_id AND root_granted.mem_id = '3'))
GROUP BY root_tags.tag_id, root_tags.tag_name
ORDER BY CountTags DESC

Since the not exists and exists are mutually exclusive, you can combine them using OR for a single query.