Group Concatenated Join

[JonnoTheDev]

I am trying to generate a report that will assist a client in determining price changes for their products. The query I need to use is bugging the hell out of me. I have extracted a portion of it as the other parts are irrelevant. What I am trying to do is display the number of times a product has been ordered of a certain quantity. I am attempting to concatenate the quantity with the number ordered. i.e.

 

product_model_id    model_code    qty_orders

----------------------     ---------------     -------------

12                            12345              1:32,2:12,3:2

 

So for the above product (12) the following info is returned

A quantity of 1 has been ordered 32 times

A quantity of 2 has been ordered 12 times

A quantity of 3 has been ordered 2 times

 

I can pull the qty_orders data out without issue by specifying the id of the product, however I need to display for all products including the additional information (product_model_id, model_code).

 

Here is the database structure:

 

product_model

-------------

product_model_id

model_code

 

order_product

-------------

op_id

order_id

product_model_id

quantity

 

I know that I need to do the sub query within a join but I can't get it quite right.

 

 

Here is the part I have down. I need to get rid of the WHERE product_model_id=12 bit and create a join to product_model so I can display all products.

SELECT 
GROUP_CONCAT(quantity, ':', total_ordered) AS qty_orders
FROM 
(
 SELECT
 quantity, COUNT(op_id) AS total_ordered 
 FROM 
 order_product
 WHERE 
 product_model_id=12
 GROUP BY quantity
) AS sq

Complex SQL isn't really my forte so any help would be appreciated.

[Barand]

JOIN to the subquery on product

SELECT
product_model_id
, model_code
, GROUP_CONCAT(quantity, ':', total_ordered) AS qty_orders
FROM
product_model
LEFT JOIN
    (
     SELECT
       product_model_id
     , quantity
     , COUNT(op_id) AS total_ordered 
     FROM 
     order_product
     GROUP BY product_model_id,quantity
    ) AS sq USING (product_model_id)
GROUP BY product_model_id;

[JonnoTheDev]

Legend!

 

Had to make a slight mod as, 'quantity' was ambiguous.

 

I owe you a pint, cheers!

SELECT
product_model_id,model_code,GROUP_CONCAT(qty, ':', total_ordered ORDER BY qty ASC) AS qty_orders
FROM
product_model
LEFT JOIN
    (
     SELECT
       product_model_id,
       quantity AS qty,
       COUNT(op_id) AS total_ordered 
     FROM 
     order_product 
     GROUP BY product_model_id, qty      
    ) AS sq USING (product_model_id)
GROUP BY product_model_id;