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

data & reporting
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