count the number of files by number of categories, category type, media type
ActivePublic
Actions

Authored by • chelsyx on Nov 7 2017, 8:42 PM.

Tags

None

Referenced Files

	F10665792: count the number of files by number of categories, category type, media type
	Nov 7 2017, 8:42 PM

Subscribers

None

	SELECT img_media_type, cat_type, n_categories, COUNT(*) AS n_files
	FROM image LEFT JOIN (
	-- count n_categories for each file
	SELECT page.page_title,
	IF(is_hiddencat = 'hiddencat', 'hiddencat', 'othercat') AS cat_type,
	COUNT(*) AS n_categories
	FROM page INNER JOIN categorylinks ON page.page_id=categorylinks.cl_from
	LEFT JOIN (
	-- hidden cat name
	SELECT page_title, pp_propname AS is_hiddencat
	FROM page INNER JOIN page_props ON page_props.pp_page=page.page_id
	WHERE pp_propname = 'hiddencat'
	AND page_namespace = 14
	) AS hidden_cat ON categorylinks.cl_to = hidden_cat.page_title
	WHERE cl_type = 'file'
	AND INSTR(LOWER(cl_to), 'needing_categor') <= 0 -- exclude need cat/need cat review
	AND page_namespace = 6
	GROUP BY page.page_title, cat_type
	) AS files ON image.img_name = files.page_title
	GROUP BY img_media_type, cat_type, n_categories;

count the number of files by number of categories, category type, media typeActivePublicActions