mysql select count(column) where sum(column) > value

3 Views

I’m trying to query $wpdb to get back an int value of the number of users in a custom table who have recorded a number of hours volunteer work above a set target – these hours need to have been moderated ( value set to = 1 ) – I have this so far:

EDIT – updated to use consistent {} around php variables in query —

Read More
$target = get_post_meta($post->ID, 'target', true) ? (int)get_post_meta($post->ID, 'target', true) : 100;

$awards = $wpdb->get_var("
    SELECT user_id 
    FROM {$this->options['rewards_logging']} 
    WHERE moderated = 1 AND reward_id = {$post->ID} 
    GROUP BY user_id 
    HAVING sum(hours) > {$target}
");

Which returns the correct value of ‘0’ if none of the hours are approved ( moderated = 0 ), however as soon as one of those users hours are approved, this query returns the count of all the users who have logged more than the target hours ( whether they have been approved or not ).

Any pointers!

Cheers

Ray

Post Views: 3

Related posts

Leave a Reply

1 comment

  1. Seems I was trying to get back a single variable using $wpdb->get_var, when I really needed the whole result set:

    $awards = $wpdb->get_results("
     SELECT user_id
     FROM {$this->options['rewards_logging']} 
     WHERE moderated = 1 AND reward_id = {$post->ID} 
     GROUP BY user_id
     HAVING sum(hours) > {$target}
");

    Then I can check over the data and display a result – etc…:

    if ( count($awards) > 0 ) {

     #var_dump($awards);
     echo '<span class="awards-notice">'.count($awards).'</span>';
} else {
     echo '-';
}