ExpressionEngine: Listing categories' total posts...

ee.categories_posts.php

<?php

/*
*****************************************************
ExpressionEngine: Listing categories' total posts... 
*****************************************************
*/

$category_group = 1;

$sql =<<<EndQuery
SELECT 
ee_categories.cat_name, 
ee_categories.cat_url_title, 
COUNT(ee_category_posts.entry_id) AS count 
FROM ee_categories 
LEFT JOIN ee_category_posts 
ON ee_categories.cat_id = ee_category_posts.cat_id 
WHERE ee_categories.group_id = {$category_group} AND ee_categories.parent_id = 0 
GROUP BY ee_categories.cat_name;
EndQuery;

$query = $DB->query($sql);

foreach($query->result as $row) {
     print '<li><a href="/category/' . $row['cat_url_title'] . '">' 
     	   . htmlentities($row['cat_name'], ENT_QUOTES) . ' [' . $row['count'] . ']</a></li>' . "\n";
}

?>