Comment count unpublished I want to display the number of unpublished comments in a template. I don't want to execute unnecessary queries on the database. Is there an already available function for this that I can use in page.tpl.php?

function comment_count_unpublished() {
  $count = db_query('SELECT COUNT(cid) FROM {comment} WHERE status = :status', array(
    ':status' =&gt; COMMENT_NOT_PUBLISHED,
  ))-&gt;fetchField();
  return t('Unapproved comments (@count)', array('@count' =&gt; $count));
}
I don't think there's anything already in page.tpl.php but it really depends on what is happening in the preprocessor(s) of your theme. I don't know what theme you're using.

If you're worried about calling a function like comment_count_unpublished() multiple times in one page load why not statically cache the result with drupal_static()?

Have you actually profiled that query and shown that it will cause problems on your site? Drupal generally runs hundreds of queries each page load so I don't think counting the unpublished comments in that table would have a noticeable impact on most sites.