USE commonswiki_p;

SELECT

  COUNT(*), SUM(page_is_redirect=0) AS Files,

  REGEXP_REPLACE(page_title, ".*?((&[^;]{2,16};)+).*", "\\1") AS HTML_Entity,

  page_title

FROM page

WHERE page_namespace=6

AND page_is_redirect=0

AND page_title REGEXP "&[_-]*(x?[0-9a-fA-F]{2,6}|amp|quot);"

GROUP BY HTML_Entity

ORDER BY Files DESC

LIMIT 100;