I have a custom field called "points." This is a numerical value, so some posts have 2 points, some have 10, etc.
I want to count the total number of "points" for all of the posts within a given category who also have the custom field "cf_Category" with the value of "Chapter Building"
So, maybe query all the posts in Category X, whose value for meta key "cf_Category" is "Chapter Building" and then add up the values for the meta key "Points" and then echo that number.
Does that make sense? I'm able to add up all of the points for all posts, but am having trouble limiting it to one category.
Thanks in advance!
Best How To :
Sounds like you need a subquery. In SQL, you want something like this:
SELECT sum(meta_value) from wp_postmeta
WHERE meta_key = "points"
AND post_id in (SELECT object_id from wp_term_relationships
WHERE term_taxonomy_id = 6)
AND post_id in (SELECT post_id from wp_postmeta
WHERE meta_key = "cf_Category" and meta_value = "Chapter Building")
The inner query will return a list of post_ids that in the taxonomy with id 6. Those post ids are then used as a condition for the query that sums the points field.
For reference, the object_id field in the wp_term_relationships table is generally the same as a post's ID, and the term_taxonomy_id in that table is generally the same as the term_id. If you don't know the term_id of a category or tag, you can find it by looking at the URL of the page when you edit that category; it's in there as a parameter called "tag_ID".
If you're not familiar with writing custom SQL queries in Wordpress, the Codex has some details. You'll want to use $wpdb->prepare().
Final code per OP
<?php $meta_key = 'cf_PointValue'; $point_total = $wpdb->get_var( $wpdb->prepare( " SELECT sum(meta_value) from wp_postmeta WHERE meta_key = %s AND post_id in (SELECT object_id from wp_term_relationships WHERE term_taxonomy_id = 17) AND post_id in (SELECT post_id from wp_postmeta WHERE meta_key = 'cf_Category' and meta_value = 'Chapter Building')", $meta_key ) ); echo $point_total; ?>