/*USE enwiki;*/

select count(el_to)as anz, l as tld 

from (

SELECT  el_to, substring(el_to,16,2) as l

FROM externallinks as eur 

   INNER JOIN page as pr on eur.el_from=pr.page_id

  WHERE pr.page_namespace=0 

  and left(el_to,18) in ('http://archive.ec/','http://archive.fo/','http://archive.is/','http://archive.li/'

                         ,'http://archive.md/','http://archive.ph/','http://archive.vn/')  

  and LENGTH(el_to) in (22,23,24)   

UNION all

SELECT  el_to, substring(el_to,17,2) as l

FROM externallinks as eur 

   INNER JOIN page as pr on eur.el_from=pr.page_id

  WHERE pr.page_namespace=0 

  and left(el_to,19) in ('https://archive.ec/','https://archive.fo/','https://archive.is/','https://archive.li/'

                         ,'https://archive.md/','https://archive.ph/','https://archive.vn/')  

  and LENGTH(el_to) in (23,24,25) 

 UNION all

SELECT  el_to, substring(el_to,16,5) as l

FROM externallinks as eur 

   INNER JOIN page as pr on eur.el_from=pr.page_id

  WHERE pr.page_namespace=0 and left(el_to,21)= 'http://archive.today/' and LENGTH(el_to) in (25,26,27)   

 UNION all

SELECT  el_to, substring(el_to,17,5) as l

FROM externallinks as eur 

   INNER JOIN page as pr on eur.el_from=pr.page_id

  WHERE pr.page_namespace=0 and left(el_to,22)= 'https://archive.today/' and LENGTH(el_to) in (26,27,28) 

  UNION all

SELECT  el_to, substring(el_to,16,2) as l

FROM externallinks as eur 

   INNER JOIN page as pr on eur.el_from=pr.page_id

  WHERE pr.page_namespace=0 

  and left(el_to,18) in ('http://archive.ec/','http://archive.fo/','http://archive.is/','http://archive.li/'

                         ,'http://archive.md/','http://archive.ph/','http://archive.vn/')  

    and (el_to  LIKE 'http://archive.__/____#selection-___%' or el_to  LIKE 'http://archive.__/_____#selection-___%')

 UNION all

SELECT  el_to, substring(el_to,17,2) as l

FROM externallinks as eur 

   INNER JOIN page as pr on eur.el_from=pr.page_id

  WHERE pr.page_namespace=0 

    and left(el_to,19) in ('https://archive.ec/','https://archive.fo/','https://archive.is/','https://archive.li/'

                         ,'https://archive.md/','https://archive.ph/','https://archive.vn/')  

    and (el_to  LIKE 'https://archive.__/____#selection-___%' or el_to  LIKE 'https://archive.__/_____#selection-___%')

 UNION all

SELECT  el_to, substring(el_to,16,5) as l

FROM externallinks as eur 

   INNER JOIN page as pr on eur.el_from=pr.page_id

  WHERE pr.page_namespace=0 and left(el_to,21)= 'http://archive.today/' 

  and (el_to  LIKE 'http://archive.today/____#selection-___%' or el_to  LIKE 'http://archive.today/_____#selection-___%')

 UNION all

SELECT  el_to, substring(el_to,17,5) as l

FROM externallinks as eur 

   INNER JOIN page as pr on eur.el_from=pr.page_id

  WHERE pr.page_namespace=0 and left(el_to,22)= 'https://archive.today/' 

  and (el_to  LIKE 'https://archive.today/____#selection-___%' or el_to  LIKE 'https://archive.today/_____#selection-___%')

) as shorts

group by l