Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I need to sum custom field values of a custom post type with a particular taxonomy ONLY.

For example...

  • Custom Post Type: Computers
  • Taxonomy: Laptops
  • Custom Field: Quantity

How can I get the quantity value of "laptop" "computers" only? I have been using the following to sum custom fields of ALL taxonomies... can it be modified to restrict to certain taxonomies?

$total_quantity = array();
$meta_key = 'inventory-quantity'; //custom field meta key
$total_quantity = $wpdb->get_col($wpdb->prepare("SELECT meta_value FROM $wpdb->postmeta WHERE meta_key = %s", $meta_key));
echo 'QTY '.array_sum( $total_quantity );

Also just tried this as suggested and get a value of 0 returned..

$total_puppies = array();
$meta_key = 'wpcf-litter-puppies';//set this to your custom field meta key
$total_puppies = $wpdb->get_col($wpdb->prepare
("SELECT meta_value FROM wp_postmeta as pm
INNER JOIN wp_term_relationships as tr ON (pm.post_id = tr.object_id) 
INNER JOIN wp_term_taxonomy as tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) 
WHERE 1
AND tt.taxonomy = 'litter-type'
AND pm.meta_key = %s", 
$meta_key));
echo 'Total Puppies '.array_sum( $total_puppies );
share|improve this question

2 Answers 2

You would have to JOIN a couple of tables.

SELECT meta_value FROM wp_postmeta as pm
INNER JOIN wp_term_relationships as tr ON (pm.post_id = tr.object_id) 
INNER JOIN wp_term_taxonomy as tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) 
WHERE 1
AND tt.taxonomy = 'laptop'
AND pm.meta_key = 'quantity'

I am fairly sure that is right. These things are complicated.

If your meta_value is a number-- that is, no punctuation or other spaces-- you should be able to use SUM(meta_value) and avoid the array_sum after the fact.

share|improve this answer
    
Thanks! I will give it a shot! –  mcseay May 27 '13 at 15:35
    
That didn't work... or either I did it wrong... here is the code I used - results was 0...<?php $total_puppies = array(); $meta_key = 'wpcf-litter-puppies';//set this to your custom field meta key $total_puppies = $wpdb->get_col($wpdb->prepare ("SELECT meta_value FROM wp_postmeta as pm INNER JOIN wp_term_relationships as tr ON (pm.post_id = tr.object_id) INNER JOIN wp_term_taxonomy as tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) WHERE 1 AND tt.taxonomy = 'litter-type' AND pm.meta_key = 'puppies'", $meta_key)); echo 'Total Puppies '.array_sum( $total_puppies ); ?> –  mcseay May 27 '13 at 17:05
    
edit your question please. Code in the comments is nearly impossible to read. –  s_ha_dum May 27 '13 at 17:06
    
You are using $wpdb->prepare but there is no replacement marker in your query. AND pm.meta_key = 'puppies' should be AND pm.meta_key = %s –  s_ha_dum May 27 '13 at 17:09
    
Sorry wrong code, this is what I used... <?php $total_quantity = array(); $meta_key = 'inventory-quantity'; //custom field meta key $total_quantity = $wpdb->get_col($wpdb->prepare ("SELECT meta_value FROM wp_postmeta as pm INNER JOIN wp_term_relationships as tr ON (pm.post_id = tr.object_id) INNER JOIN wp_term_taxonomy as tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) WHERE 1 AND tt.taxonomy = 'laptop' AND pm.meta_key = 'quantity'", $meta_key)); echo 'QTY '.array_sum( $total_quantity ); ?> –  mcseay May 27 '13 at 17:11

I couldn't get the SQL statements to work so I came up with another approach (not the best way to do this, but it works)...

<?php 
$args = array(
    'post_type' => 'puppies',
    'taxonomy' => 'litter-type',
    'term' => 'previous-litters',
    'posts_per_page' =>-1
);
$loop = new WP_Query($args);
if ( $loop->have_posts() ) : ?> 
<?php    
while ( $loop->have_posts() ) : $loop->the_post(); ?>
    <?php $amt = get_post_meta($post->ID, 'wpcf-litter-puppies', true); ?> 
    <?php if ($amt) {$previous_puppies_total += $amt;}; ?>
    echo $total; ?>
<?php endwhile; ?>
<?php echo 'Puppies from previous litters: '.$previous_puppies_total; ?>
<?php endif; ?>
<?php wp_reset_postdata(); ?>
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.