MySQL - Wordpress > how to get posts by post_status / How to sum meta_values

Question

Hello MySQL specialists,

I am working on a wordpress website referencing used vehicles.

I have a table called "wp_686591_postmeta" in which I have various data including the 'brand' and the 'selling price'.

I wrote the below sql query in phpMyAdmin:

SELECT pm.meta_value as 'brand', COUNT(pm.meta_key) as 'number of vehicles' FROM wp_686591_postmeta pm WHERE pm.meta_key = 'brand' GROUP BY pm.meta_value

As a result I get two columns : the car brand and the total number of vehicles for each brand no matter what the post_status is.

I need to add a third column that sums all selling prices of each brand. The meta_key is "selling-price".

Because I am very new in MySQL, I am struggling to do two things :

1. I need to add a third column called "Total" to display the sum of all selling prices for each brand.

The result should be :

2. I need to do the same but by post_status "publish" and "pending".

I hope someone may assist. Thanks a lot.

Answer 1

much better if you have created brand as taxonomy, so you could have easily queried the taxonomy, get all posts belongs to that taxonomy then sum up their meta value based on key for price.

Anyway, your queries wont work since you also have to check for post status as well as another entries on the same post meta table. you need to first query the post table, and separately join brand meta and price meta,

assuming you have your own custom post type for vehicles listing, something like this could work

SELECT 
    COALESCE(brands.meta_value,'Un-branded') AS Brand,
    COUNT(  DISTINCT vehicles.id) AS 'Number of Vehicles',
    COALESCE(SUM( sp.meta_value), 0) AS 'Total in €'

FROM WHATEVER_YOUR_POST_TABLE_IS AS vehicles

LEFT JOIN wp_686591_postmeta AS sp
    ON vehicles.ID = sp.post_id 
    AND sp.meta_key = 'selling-price'

LEFT JOIN wp_686591_postmeta AS brands
    ON vehicles.ID = brands.post_id 
    AND brands.meta_key = 'brand'

WHERE vehicles.post_status = 'publish' 
AND vehicles.post_type = 'your-vehicle-post-type'
GROUP BY brands.meta_value

If for the love of god you are using the default post as your vehicle listing mixing it with standard blog post, you may use INNER JOIN instead of LEFT JOIN this would filter out post if they dont have brand or price meta