How do I get a report of total topics by subcategory for all subcategories please?

data & reporting
1

Couldn’t find anything specific to my request please.

I’d just like to see total topics per subcategory for all time. It’s only been 3 years in our case.

Thank-you.

2

Have you got the data explorer on your site? If so, you should be able to pull out those numbers with something like this:

SELECT t.category_id,
       COUNT(t.category_id)-1 AS topics
FROM topics t
JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at IS NULL
AND t.visible = true
AND c.post_count IS NOT NULL
GROUP BY c.position, t.category_id
ORDER BY c.position

This one doesn’t count Unlisted or deleted topics, and ignores deleted categories based on NULL post count. It’s also in ‘position’ order, which may not be as useful if you haven’t set fixed postions.

(I’ve added in a ‘-1’ to account for the About topic)

1 Like
3

Don’t have Data Explorer, but investigating what you’ve shared.
Thank-you.

4

That replicates the numbers shown in the category dropdown list, so you could manually jot them down from there as a backup plan?

image     image

1 Like
5

The drop down window is too narrow - category descriptions are longish.
And it doesn’t give subcategories.

6

Disregard that last comment.
I see now how it’s done.
Thank-you.
That’ll help.

1 Like
7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.