1

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 );
1

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.

  • 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
  • Sorry, updated the original question... – mcseay May 27 '13 at 17:14
1

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(); ?>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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