Hello,
I’d like to ask for help because I’m a complete beginner in MYSQL.
I want to sum all Costs where Type is equal to 2 (custom ACF fields):
Type | Cost
0 | 100
2 | 150
1 | 50
2 | 150
In this example the result would be ‘300’. Then I save the result to Options so I can use it often. I was only able to sum the Cost column:
global $wpdb;
$meta_key = 'task_cost';
$cost_s = $wpdb->get_var(
$wpdb->prepare(
"
SELECT sum(meta_value)
FROM $wpdb->postmeta
WHERE meta_key = %s
",
$meta_key
)
);
update_field('sa_cost', $cost_s, 'options');
How should I modify the query to get the sum only for selected type (task_type)? Thanks!
It is practicably not possible to do what you want to do without doing multiple queries. The reason for this is that there is no connection between the two meta fields other than the post ID column of the postmata table.
First you would need to do a query to get all of the post IDs where “type” == 1 for example. Then you would need to do a query using
"WHERE post_id IN({$LIST_OF_POST_IDs) AND meta_key = 'field_name'"
Thank you for your answer! I didn’t realize limitation of this approach.
