Search the Community

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.