How to retrieve the sum from WordPress custom fields database, from the same column

1 Views

This is my query. So i need to retrieve the sum from the values of budget meta key which are only in component 1 and in year 2009. How do i do this. Thanks in advance 

SELECT SUM(
b.meta_value)
, b.meta_key
FROM wp_posts AS p, wp_postmeta AS b, wp_postmeta AS m, wp_postmeta AS n
WHERE (
p.ID = b.post_id
)
AND (
b.meta_key =  'budget'
)
AND (
m.meta_key =  'component'
AND m.meta_value =  '1'
)
AND (
n.meta_key =  'component-year'
AND n.meta_value =  '2009'
)
Post Views: 1

Related posts

Leave a Reply

1 comment

  1. OK. So I think what you are saying is that you have posts with three custom fields; “component”, “component-year”, and “budget”. You are trying to get a total of values of “budget” when component=1 and component-year=2009. If that is correct, then this works – it is not the most efficient but it works fine.

    SELECT SUM(wp_postmeta.meta_value) AS total
FROM wp_posts 
LEFT JOIN wp_postmeta ON (
    wp_posts.ID = wp_postmeta.post_id
    AND
    wp_postmeta.meta_key = 'budget'
)

# get all the posts with a custom field of "component" of "1"
WHERE wp_posts.ID IN (
    SELECT post_id
    FROM wp_postmeta
    WHERE meta_key =  'component'
    AND meta_value =  '1'
)

# and all the posts with a custom field of "component-year" of "2009"
AND wp_posts.ID IN (
    SELECT post_id
    FROM wp_postmeta
    WHERE meta_key =  'component-year'
    AND meta_value =  '2009'
);