Count posts for each year

Question

I'm using the following code to list all years from my blog, I guess we can call it an archive.

<?php
$years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts ORDER BY post_date DESC");
foreach($years as $year) : ?>
    <?php echo $year ?>
<?php endforeach; ?>

What I'd like to achieve though is to also display, in front of each year, the number of posts (count) that each year has. Example:

2015 (5 posts)
2014 (3 posts)
2011 (10 posts)

I found a hint somewhere and tried to implement it, but didn't work. Here's what I was trying:

<?php
$years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts ORDER BY post_date DESC");
$count = $years->post_count;
foreach($years as $year) : ?>
    <?php echo $year ?> (<?php echo $count ?> posts)
<?php endforeach; ?>

Any ideas?

Thank you.

EDIT @David

<?php 
/** Grab the years that contain published posts, and a count of how many */
$query = $wpdb->prepare('
            SELECT YEAR(%1$s.post_date) AS `year`, count(%1$s.ID) as `posts`
            FROM %1$s
            WHERE %1$s.post_type IN ("post")
            AND %1$s.post_status IN ("publish")
            GROUP BY YEAR(%1$s.post_date)
            ORDER BY %1$s.post_date DESC',
            $wpdb->posts
        );
$results = $wpdb->get_results($query);

/** Create the '$years' array, a list of all yearly archives */
$years = array();
if(!empty($results)) : foreach($results as $result) :

        $url = get_year_link($result->year);                            // The archive link URL for the current year
        $text = $result->year;                                          // The archive link text for the current year
        $count = $result->posts;                                        // The number of posts in the current year
        $years[] = get_archives_link($url, $text, 'html', '', $count);  // Create the archive link for the current year

    endforeach;
endif;
?>
<a href="<?php echo $url; ?>"><li><?php echo $text ?> <span class="lower">(<?php echo $count ?>)</span></li></a>

Answer 1

The Easy Way

This is by far and away the easiest approach to this, but unless you are willing to be a little bit flexible with how you present your archive it's not for you.

The easy solution is achieved using the wp_get_archives() function. Unfortunately though there is no filter available to manipulate the output, so you'll be left with a list like this. The count will not be part of the link -

2015 (5)
2014 (3)
2011 (10)

And here is the code -

$args = array(
    'type'              => 'yearly',
    'show_post_count'   => true
);
wp_get_archives($args);

The Hard Way

So if you decide the easy way isn't for you that only leave the hard way. Essentially we are replicating the functionality of wp_get_archives(), but you can format the output exactly as you wish.

The code below makes use of a custom query, not dissimilar from your own (except that we grab the count of posts as well as the years), as well as the functions get_year_link() and get_archives_link().

The output will leave you with a list formatted as you posted in the update to your question. The count will be part of the link -

2015 (5)
2014 (3)
2011 (10)

And here is the code -

/** Grab the years that contain published posts, and a count of how many */
$query = $wpdb->prepare('
            SELECT YEAR(%1$s.post_date) AS `year`, count(%1$s.ID) as `posts`
            FROM %1$s
            WHERE %1$s.post_type IN ("post")
            AND %1$s.post_status IN ("publish")
            GROUP BY YEAR(%1$s.post_date)
            ORDER BY %1$s.post_date',
            $wpdb->posts
        );
$results = $wpdb->get_results($query);      

/** Create the '$years' array, a list of all yearly archives */
$years = array();
if(!empty($results)) : foreach($results as $result) :

        $count = '<span class="lower">(' . $result->posts . ')</span>';      // The number of posts in the current year
        $url = get_year_link($result->year);                                    // The archive link URL for the current year
        $text = '<li>' . $result->year . ' ' . $count . '</li>';                // The archive link text for the current year
        $years[] = get_archives_link($url, $text, 'html');                      // Create the archive link for the current year

    endforeach;
endif;

/** Output the custom archive list */
echo join("\n", $years);

Edit

Based on your comment, I've amended my answer above slightly.

The reason that you were only able to output one year is because you were only explicity echoing the last year after the foreach loop. What the code does is add each year to the $years array, and then outputs all of them using the final line - echo join("\n", $years);

Answer 2

Just had simmilar problem and because I don't feel comfortable with performing DB queries in rendered php sites, I made this function, witch uses wp_get_archives() and regex. It returns array with year as the key and number of posts as the value.

The middle way:

function load_years() {
  $args = array(
    'type'              => 'yearly',
    'show_post_count'   => true
  );
  ob_start();
  wp_get_archives($args);
  $item = ob_get_contents(); 
  ob_end_clean(); 
  $pattern = '/\'>(\d\d\d\d)<.*nbsp;\((\d*)/';
  preg_match_all($pattern, $item, $matches, PREG_OFFSET_CAPTURE);
  $years_array = array();
  foreach ($matches[1] as $key => $value) {
      $year = $matches[1][$key][0];
      $posts = $matches[2][$key][0];
      $years_array[$year] = $posts;
  }
  return $years_array;
}