SET max_recursive_iterations=2;

WITH results AS (

WITH RECURSIVE artred AS (

SELECT talkpage.page_title AS art_title, talkpage.page_title AS red_title

  FROM templatelinks

  JOIN page AS talkpage

    ON talkpage.page_id = tl_from

  JOIN page AS artpage

    ON artpage.page_namespace = 0 AND artpage.page_title = talkpage.page_title

LEFT JOIN page_props

    ON pp_page = artpage.page_id AND pp_propname = 'disambiguation'

 WHERE tl_from_namespace = 1

   AND tl_namespace = 10

    AND tl_title = 'WikiProject_Military_history'

   AND artpage.page_is_redirect = 0

   AND pp_page IS NULL

 UNION

SELECT art_title, page_title AS red_title

  FROM artred

  JOIN redirect

    ON rd_title = art_title

  JOIN page

    ON page_id = rd_from

 WHERE rd_namespace = 0

   AND page_namespace = 0

)

​

SELECT COUNT(inlinkpage.page_id) AS inlinks, REPLACE(art_title, "_", " ") AS title,

  SUBSTRING(REPLACE(GROUP_CONCAT(DISTINCT (CASE red_title WHEN art_title THEN "" ELSE red_title END)

   ORDER BY (CASE red_title WHEN art_title THEN "" ELSE red_title END) SEPARATOR '|'), "_", " ") FROM 2) AS redirects

FROM artred

LEFT JOIN pagelinks

  ON pl_namespace = 0 AND pl_title = red_title AND pl_from_namespace = 0

LEFT JOIN page AS inlinkpage

  ON inlinkpage.page_id = pl_from AND inlinkpage.page_is_redirect = 0

GROUP BY art_title

HAVING COUNT(inlinkpage.page_id) <= 10

ORDER BY inlinks ASC, title ASC

)

SELECT inlinks, "(Total)" AS title, CAST( COUNT(*) AS char) AS redirects FROM results GROUP BY inlinks

UNION

SELECT inlinks, title, redirects FROM results