Mysql query rows to columns but columns separed by meta_key

Question

i've got an itemmeta table like this:

item_id meta_key meta_value 1 variation_id 123 1 qty 3 2 variation_id 100 2 qty 2 3 variation_id 123 3 qty 4

What i need:

123 7 100 2

I can do what i want with a subquery like:

 SELECT DISTINCT m1.meta_value AS variation_id, ( SELECT SUM(m2.meta_value) FROM itemmeta m2 WHERE m2.meta_key='qty' AND m1.item_id = m2.item_id ) AS qty FROM itemmeta m1 WHERE m1.meta_key = 'variation_id'

But i wondering are there any better, simpler or more efficient solution?

Thank you!

please copy your data as text, we cant copy / paste from images. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Oct 27, 2016 at 15:07
that kind of subquery can be rewritten as a conventional join with group by. — Marc B
– Marc B, Commented Oct 27, 2016 at 15:07

Chris Lear · Accepted Answer · 2016-10-27 15:13:42Z

Here's how to do it with a join

 select m.meta_value, sum(m2.meta_value) from itemmeta m join itemmeta m2 on m.item_id = m2.item_id and m.meta_key = 'variation_id' and m2.meta_key = 'qty' group by m.meta_value;

Strawberry · Accepted Answer · 2016-10-27 15:22:48Z

E.g. - though this isn't the fastest method

SELECT variation_id , SUM(qty) qty FROM ( SELECT MAX(CASE WHEN meta_key = 'variation_id' THEN meta_value END) variation_id , MAX(CASE WHEN meta_key = 'qty' THEN meta_value END) qty FROM my_table GROUP BY item_id ) x GROUP BY variation_id;

Collectives™ on Stack Overflow

Mysql query rows to columns but columns separed by meta_key

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related