SELECT SUBSTRING_INDEX(page_title, '_', 1) AS type,

       actor_name AS name, 

       COUNT(DISTINCT page_title) AS 'count',

       NULL AS titles

FROM revision_userindex

JOIN actor_revision ON actor_id = rev_actor

JOIN page ON page_id = rev_page

WHERE actor_name IN ('Mark Ironie', 'CorbieVreccan')

  AND page_namespace = 4

  AND ((page_title LIKE 'Articles_for_deletion/%' AND page_title NOT LIKE 'Articles_for_deletion/Log/%')

       OR page_title LIKE 'Categories_for_discussion/%'

       OR page_title LIKE 'Categories_for_deletion/%'

       OR page_title LIKE 'Templates_for_discussion/%'

       OR page_title LIKE 'Templates_for_deletion/%'

       OR page_title LIKE 'Redirects_for_discussion/%'

       OR page_title LIKE 'Redirects_for_deletion/%'

       OR page_title LIKE 'Miscellany_for_deletion/%')

GROUP BY SUBSTRING_INDEX(page_title, '_', 1), actor_name

​

UNION

​

SELECT SUBSTRING_INDEX(page_title, '_', 1),

       'Both',

       COUNT(DISTINCT page_title),

       GROUP_CONCAT(DISTINCT CONCAT('[[WP:', page_title, ']]') SEPARATOR ' ')

FROM revision_userindex AS rmi

JOIN actor_revision AS ami ON ami.actor_id = rmi.rev_actor

JOIN revision_userindex AS rcv

JOIN actor_revision AS acv ON acv.actor_id = rcv.rev_actor

JOIN page ON page_id = rmi.rev_page

WHERE ami.actor_name = 'Mark Ironie'

  AND acv.actor_name = 'CorbieVreccan'

  AND rmi.rev_page = rcv.rev_page

  AND page_namespace = 4

  AND ((page_title LIKE 'Articles_for_deletion/%' AND page_title NOT LIKE 'Articles_for_deletion/Log/%')

       OR page_title LIKE 'Categories_for_discussion/%'

       OR page_title LIKE 'Categories_for_deletion/%'

       OR page_title LIKE 'Templates_for_discussion/%'

       OR page_title LIKE 'Templates_for_deletion/%'

       OR page_title LIKE 'Redirects_for_discussion/%'

       OR page_title LIKE 'Redirects_for_deletion/%'

       OR page_title LIKE 'Miscellany_for_deletion/%')

GROUP BY SUBSTRING_INDEX(page_title, '_', 1)

​

ORDER BY type, name;

​