MODX count number of times a template is used

modx_site_structure_pretty.sql

SELECT a.id AS 'ID',
       CASE
           WHEN a.menutitle = '' THEN a.pagetitle
           ELSE a.menutitle
       END AS 'Title',
       a.uri AS 'URI',
       c.templatename AS 'Template',
       CASE
           WHEN a.hidemenu = 1 THEN 'Hidden'
           ELSE 'Visible'
       END AS 'Visibility',
       COUNT(b.id) AS 'Immediate Children'
FROM modx_site_content a
LEFT JOIN modx_site_content b ON a.id = b.parent AND b.published = 1 AND b.deleted = 0
LEFT JOIN modx_site_templates c ON a.template = c.id
WHERE a.published = 1 AND a.deleted = 0 AND a.parent = 0
GROUP BY a.id, a.menuindex
ORDER BY a.menuindex ASC;

modx_site_templates.sql

SELECT a.id, a.templatename, COUNT(b.id) as resources FROM modx_site_templates AS a
LEFT JOIN modx_site_content AS b ON a.id = b.template AND b.published = 1 AND b.deleted = 0
WHERE b.id IS NOT NULL
GROUP BY a.id;

modx_site_tmplvars.sql

SELECT a.id, a.type, a.name, COUNT(c.id) FROM modx_site_tmplvars a
LEFT JOIN modx_site_tmplvar_contentvalues b ON a.id = b.tmplvarid
LEFT JOIN modx_site_content c ON b.contentid = c.id AND c.published = 1 AND c.deleted = 0
WHERE c.id IS NOT NULL
GROUP BY a.id