Most common categories (with WD qids) for Commons file pages linked from a page

Fork of Most common categories for Commons file pages linked from a page by Jheald

This query is marked as a draft This query has been published by Jheald.

SQL

use commonswiki_p;

SELECT COUNT(DISTINCT(pl.pl_title)) AS $count, cl.cl_to, cpp.pp_value  

FROM page AS p0
JOIN pagelinks AS pl ON pl.pl_from=p0.page_id AND pl.pl_from_namespace=p0.page_namespace AND pl.pl_namespace=6
               # links to file description pages
JOIN page AS fp ON fp.page_namespace = pl.pl_namespace AND fp.page_title = pl.pl_title
JOIN categorylinks AS cl ON cl.cl_from = fp.page_id
               # categories for file description pages
JOIN page AS cp ON cp.page_namespace = 14 AND cp.page_title = cl.cl_to
LEFT JOIN page_props AS cpp ON cpp.pp_page = cp.page_id AND cpp.pp_propname = 'wikibase_item'


WHERE p0.page_title='British_Library/MC_maps_batch_01_(GB_counties)' AND p0.page_namespace=4
GROUP BY cl.cl_to, cpp.pp_value
ORDER BY $count DESC

SQL

Checking query status...