Views by sub-level category
A more granular analysis of topic views, but without summing them up. Entries with sub-category “(none)” relate to topics placed directly into the top-level categories.
SELECT
topcat AS "Category",
subcat AS "Subcategory",
SUM(views) AS "Subcategory views"
FROM (
/* Topic views in sub-level categories. */
SELECT
topcat.name AS topcat, subcat.name AS subcat, SUM(topics.views) AS views
FROM topics
INNER JOIN categories subcat ON topics.category_id = subcat.id
INNER JOIN categories topcat ON subcat.parent_category_id = topcat.id
GROUP BY topcat.name, subcat.name
UNION
/* Topic views in top-level categories (excluding sub-level cats). */
SELECT
topcat.name AS topcat, '(none)', SUM(topics.views) AS views
FROM topics
INNER JOIN categories topcat ON topics.category_id = topcat.id
WHERE topcat.parent_category_id IS NULL
GROUP BY topcat.name
) AS views_by_cat
GROUP BY topcat, subcat
/* Sort the output by either category or views. Enable one of these: */
/* ORDER BY topcat, subcat */
ORDER BY "Subcategory views" DESC