use trwiki_p;

SET sql_mode='PIPES_AS_CONCAT';

select '# [['||el_to ||']]' as el_to, CONCAT('# ',GROUP_CONCAT(page_title SEPARATOR ' '),'') as page, COUNT(el_to) as cnt

from externallinks 

  inner join page

    on (page_id=el_from)

where page_namespace =0

and (el_to like '%.blogspot.%'  

  or el_to like '%.tumblr.%' 

  or el_to like '%geocities.%' 

  or el_to like '%.livejournal.%'

  or el_to like '%.tr.gg%'

  or el_to like '%.wordpress.com%'

  or el_to like '%.blogcu.com%'

  or el_to like '%.weebly.com%'

  or el_to like '%.net.tc%'

  or el_to like '%.co.cc%'

  or el_to like '%.azbuz.com%'

  or el_to like '%.somee.com%'

  or el_to like '%osmanli700.gen.tr%'

  or el_to like '%kimkimdir.gen.tr%'

  or el_to like '%ermenisorunu.gen.tr%'

  or el_to like '%canakkale.gen.tr%'

  or el_to like '%ataturk.net%'

  or el_to like '%turkcebilgi.com%'

  or el_to like '%.k12%'

  or el_to like '%birimcevir.com%'

  or el_to like '%eksisozluk.%'

  or el_to like '%.sourtimes.%'

  or el_to like '%onedio.com%' 

  or el_to like '%.tripod.com%'

  or el_to like '%jenerator.name.tr%'

  or el_to like '%muhendisbeyinler.net%'

  or el_to LIKE '%uludagsozluk.com%'  

  or el_to LIKE '%savsatli.com%'

  or el_to LIKE '%karakocannostalji.com%'

  or el_to LIKE '%alisarkoyu.com%'

  or el_to LIKE '%akcakocam.com%'

  or el_to LIKE '%sivricehaber.com%'

  or el_to LIKE '%karamanlive.com%'

  or el_to LIKE '%sozlukspot.com%'

  or el_to LIKE '%.wikia.com%'

  or el_to LIKE '%itusozluk.com%'

  or el_to LIKE '%quora.com%'

  or el_to LIKE '%answers.com%'

  or el_to LIKE '%sindirella.com%'

  or el_to LIKE '%haberbedava.com%'

  or el_to LIKE '%ensonhaber.com%'

  or el_to LIKE '%haberler.com%'

  or el_to LIKE '%anlatio.com%'

  or el_to LIKE '%listelist.com%'

  )

AND (el_to NOT LIKE '%googleblog.blogspot%'

  AND el_to NOT LIKE '%googlepress.blogspot%'

  AND el_to NOT LIKE '%googleforwork.blogspot%'

  AND el_to NOT LIKE '%googledocs.blogspot%'

  AND el_to NOT LIKE '%googledrive.blogspot%'

  AND el_to NOT LIKE '%nisanyan1.blogspot%'

  AND el_to NOT LIKE '%google-opensource.blogspot%'

  AND el_to NOT LIKE '%gmailblog.blogspot%'

  AND el_to NOT LIKE '%googlesystem.blogspot%'

  AND el_to NOT LIKE '%nisanyan.blogspot%'

  AND el_to NOT LIKE '%googleonlinesecurity.blogspot%'

  AND el_to NOT LIKE '%googleresearch.blogspot%'

  AND el_to NOT LIKE '%googlemobile.blogspot%'

  AND el_to NOT LIKE '%googlevideo.blogspot%'

  AND el_to NOT LIKE '%wikimapiablog.blogspot%'

)

GROUP BY page_title

ORDER BY cnt desc;