Post count by meta_key meta_value

Question

I have 100 posts to the ( states meta_key ), and different meta_value (AK,AR,CA,FL,HW,MN,SC,TX,MN,NJ). I want to show the post count on my site sidebar for posts AK-11 posts,AR-9 posts....

Answer 1

// retrieve all meta_values with key 'state' from database
$state_posts = $wpdb->get_results("
    SELECT meta_value FROM ".
    $wpdb->prefix."postmeta
    WHERE meta_key = 'state'
    ORDER BY meta_value ASC",
    ARRAY_A
);

// define counting array
$state_count = array();

// iterate through meta_values, count the occurence of each state
foreach ( $state_posts as $state_post ) {
    if ( isset ( $state_count[$state_post['meta_value']] ) ) {
        $state_count[$state_post['meta_value']] = $state_count[$state_post['meta_value']] + 1;
    } else {
        $state_count[$state_post['meta_value']] = 1;
    }
}

// echo results
echo '<ul>';
foreach ( $state_count as $state => $count ) {
     echo '<li>' . $state . ': ' . $count . ' posts</li>';
}
echo '</ul>';

Answer 2

A slightly quicker version of the two other answers above: count the posts in your MySQL query.

$state_query = $wpdb->get_results("
            SELECT meta_value AS state, COUNT(post_id) AS count
            FROM {$wpdb->postmeta} WHERE meta_key = 'state'
            GROUP BY state ORDER BY state ASC");

if ($state_query) {
    echo '<ul>';
    foreach ($state_query as $st) 
        echo '<li>' . $st->state . ' - ' . $st->count . ' posts' . '</li>';
    echo '</ul>';
}