MYSQL query display quantity of product from multiple tables

Question

I have table like this

product :

id_product  | product_name      | price

560         | AAA               | 1500 
561         | BBB               | 1750

attr :

id_attr | id_product | size | qty

100     | 560        | S    | 11

100     | 560        | M    | 9

100     | 560        | L    | 7

100     | 560        | XL   | 21

How to display

product_name | qty_S | qty_M | qty_L | qty_XL | qty_total | price

AAA          | 11    | 9     | 7     | 21     | 48        | 1500

BBB          | 0     | 0     | 0     | 0      | 0         | 1750

i try to make query like this :

select p.*, a.*,

sum(a.qty) as qty_total,

[how_to_display_query] as qty_S,

[how_to_display_query] as qty_M,

[how_to_display_query] as qty_L,

[how_to_display_query] as qty_XL,


FROM product p LEFT JOIN attr a
ON p.id_product = a.id_product
group by p.id_product;

===============================================

Please help me, sorry for bad english... Thanks

Seems like a good solution can be found here: http://stackoverflow.com/questions/7674786/mysql-pivot-table — xDaevax, Aug 21 '14 at 17:45

John Ruddell · Accepted Answer · 2014-08-21T17:53:42.787

you want to pivot the results.. but you cant pivot in mysql as it doesn't have that functionality.. so you need to "fake" a pivot by using an aggregate and a conditional statement

the way to do that is like this.. MAX(CASE... ) SUM(CASE... ) etc.. can also be done with if MAX(IF... ) SUM(IF... ).

SELECT p.product_name
    SUM(a.qty) as qty_total,
    MAX(CASE size WHEN 'S' THEN qty ELSE 0 END) as qty_S,
    MAX(CASE size WHEN 'M' THEN qty ELSE 0 END) as qty_M,
    MAX(CASE size WHEN 'L' THEN qty ELSE 0 END) as qty_L,
    MAX(CASE size WHEN 'XL' THEN qty ELSE 0 END) as qty_XL,
    p.price
FROM product p 
LEFT JOIN attr a ON p.id_product = a.id_product
GROUP BY by p.id_product;

i used MAX() here because you only seem to be getting one number.. if you were wanting to add multiple quantities then you can change MAX to SUM

MYSQL query display quantity of product from multiple tables

1 Answers1