Views by top-level category

data & reporting
1

Views by top-level category

Including grand total of all topic views on your forum since the very beginning.

/* Outer SELECT to hide the artificial sort order column, and sort by it. */
SELECT "Category", "Views"
FROM (

    /* Views per top-level category. */
    SELECT 
        topcat AS "Category", 
        SUM(views) AS "Views",
        0 AS sortorder /* Artificial sorting column, here to sort to the beginning. */
    
    FROM (
        /* Topic views in sub-level categories. */
        SELECT
            topcat.name AS topcat, 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
        
        UNION
    
        /* Topic views in top-level categories (excluding sub-level cats). */
        SELECT
            topcat.name AS topcat, 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
    
    UNION
    
    /* Adding a TOTAL row at the end. */
    SELECT 
        'GRAND TOTAL' AS "Category",
        SUM(topics.views) AS "Views",
        1 AS sortorder /* Artificial sorting column, here to sort to the end. */
    FROM
        topics
    GROUP BY "Category"
    
    /* Sort the output by either category or views. Enable one of these: */
    /* ORDER BY topcat, subcat */
    ORDER BY "Views" DESC

) AS views_by_cat_with_total

ORDER BY sortorder, "Category"