Gouping SQL Query result-CodePudding

I have this SQL query

SELECT id, meta_key, meta_value, 
FROM  wp_posts post
INNER JOIN wp_postmeta meta 
ON post.ID = meta.POST_ID 
WHERE post.post_status = 'publish' 
AND ( meta.meta_key = 'brand' OR meta.meta_key = 'price' )

which has a result like this

id --|-- meta_key --|--  meta_value
-----------------------------------
42 --|-- brand    --|--  mercedes
44 --|-- brand    --|--  honda
46 --|-- brand    --|--  honda
42 --|-- price    --|--  20
44 --|-- price    --|--  20
46 --|-- price    --|--  50

How can I achieve a result like this?

brand --|-- UNIQUE ID COUNT  --|-- Total price sum of IDs relating to brand
----------------------------------------------
honda --|-------  2  ----------|-- 70
mercedes|-------  1  ----------|-- 20

I did try to set another result column for brand and price, but still not quite sure how to group the result

SELECT id, meta_key, meta_value,
(CASE WHEN meta_key = 'brand' THEN meta_value END) as brand,
(CASE WHEN meta_key = 'price' THEN meta_value END) as price
FROM  wp_posts post
INNER JOIN wp_postmeta meta 
ON post.ID = meta.POST_ID 
WHERE post.post_status = 'publish' 
AND ( meta.meta_key = 'brand' OR meta.meta_key = 'price' )

resulting to this

id --|-- meta_key --|--  meta_value  --|-- brand  --|--  Price 
--------------------------------------------------------------
42 --|-- brand    --|--  mercedes    --|-- mercedes |--  NULL
44 --|-- brand    --|--  honda       --|-- honda  --|--  NULL
46 --|-- brand    --|--  honda       --|-- honda  --|--  NULL
42 --|-- price    --|--  20          --|-- NULL   --|--  20
44 --|-- price    --|--  20          --|-- NULL   --|--  20
46 --|-- price    --|--  50          --|-- NULL   --|--  50

Appreciate any help

CodePudding user response：

Change your single join of wp_postmeta into two separate joins, one for brands and one for prices:

select brands.meta_value brand
    , count(distinct posts.id) unique_id_count
    , sum(price.meta_value) price
from wp_posts posts
inner join wp_postmeta brands
    on posts.id = brands.post_id
    and brands.meta_key = 'brand'
inner join wp_postmeta price
    on posts.id = price.post_id
    and price.meta_key = 'price'
WHERE posts.post_status = 'publish' 
group by brands.meta_value