# Views by sub-level category

**URL:** https://meta.discourse.org/t/views-by-sub-level-category/275173
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [March 27, 2020, 4:35pm UTC](https://meta.discourse.org/t/views-by-sub-level-category/275173 "2020-03-27T16:35:30Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![tanius](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/tanius/32/135041_2.png) [@tanius](https://meta.discourse.org/u/tanius)
#### Post date: [March 27, 2020, 4:35pm UTC](https://meta.discourse.org/t/views-by-sub-level-category/275173/1 "2020-03-27T16:35:30Z")

</div>

## 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.

```sql
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

```