Jump to content

Group Concatenated Join


JonnoTheDev
Go to solution Solved by Barand,

Recommended Posts

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.

Link to comment
Share on other sites

  • Solution

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;
Link to comment
Share on other sites

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;
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.