Problem to correctly obtain more solds and visited items in MySQL-CodePudding

I'm having trouble getting the total of most sold and total most viewed products correctly.

Case best sellers:

   SELECT p.product_id, p.title, p.description, p.specifications, p.price, p.discount, p.discount_type, p.color, p.hours, p.product_type, p.gender, p.img, p.vdo_intro, p.status, p.url, p.date_start, p.date_end, p.ip, p.product_page, p.product_code, p.brand,   p.color_url,                          
          c.category_id, c.category_url as urlcategory, c.parent,
          SUM(o.order_quantity) as solds
     FROM order_detail o
     JOIN product p ON o.product_id = p.product_id                      
     JOIN category_relations rcat ON rcat.product_id = p.product_id 
LEFT JOIN `category` c on rcat.category_id = c.category_id  
  WHERE p.gender = ? AND p.status=1 AND p.date_end IS NULL 
 GROUP BY p.product_id
 ORDER BY SUM(o.order_quantity)
     DESC LIMIT 12;

In Data Base i have:

id_order    order_quantity  order_price order_discount  order_discount_type product_id  order_date
    1           2               59.00        0               %                 1        2022-01-31 22:49:24
    2           5               59.00        0               %                20        2022-01-31 22:49:24
    3          12               59.00        0               %                 8        2022-01-31 22:49:24
    4           5               59.00        0               %                19        2022-01-31 22:49:24
    5          25               59.00        0               %                17        2022-01-31 22:49:24
    6           3               59.00        0               %                 1        2022-01-31 22:49:24

Result i get :

id:1    2   3 = 5 solds    i get = 20 solds (wrong). 
id:20   5 solds            i get = 15 solds (wrong).
id:8   12 solds            i get = 36 solds (wrong).
id:19   5 solds            i get = 5 solds (ok).
id:17  25 solds            i get = 50 solds (wrong).

Does anyone know what I'm doing wrong?
It look like that it is multiplying the results by records.

Case most visits:

    SELECT p.product_id, p.title, p.description, p.specifications, p.price, p.discount, p.discount_type, p.color, p.hours, p.product_type, p.gender, p.img, p.vdo_intro, p.status, p.url, p.date_start, p.date_end, p.ip, p.product_page, p.product_code, p.brand, p.color_url,                       
           c.category_id, c.category_url as urlcategory, c.parent,
           SUM(v.total) as visits
      FROM `visits` v
      JOIN `product` p ON v.product_id = p.product_id
      JOIN category_relations rcat ON rcat.product_id = p.product_id 
 LEFT JOIN `category` c on rcat.category_id = c.category_id                                     
     WHERE p.gender = ? AND p.status=1 AND p.date_end IS NULL  
  GROUP BY p.product_id
  ORDER BY SUM(v.total)
      DESC LIMIT 12

My database:

id_visits   total      ip              today        product_id
23           5     xxxxxxxxxxxxx1   2022-02-09        1
36           1     xxxxxxxxxxxxx4   2022-02-06       13
40           1     xxxxxxxxxxxxx3   2022-02-06       13
41           1     xxxxxxxxxxxxx1   2022-02-06       21
48           1     xxxxxxxxxxxxx2   2022-02-07       13
50           1     xxxxxxxxxxxxx2   2022-02-07        1
62           1     xxxxxxxxxxxxx8   2022-02-08        1

Result i get:

id1 :   5   1   1 = 7 visits      i get 28 visits (wrong)
id13:   1   1   1 = 3 visits      i get 9 visits (wrong)
id21:   1         = 1 visits      i get 3 visits (wrong)

Does anyone know the error, because it does not add up correctly?

note: I can add the tables if needed

Edit:

DB Fiddle

If i do:

GROUP BY rcat.cat_relation_id

I get the calculation right, but it repeats the same products

CodePudding user response：

Your problem is that category_relations is a many to many table. So when you join it you get additional results for each category that a product is in. So product id1 is in 4 categories so you get a sum of 28 instead of 7.

CodePudding user response：

I think you can try to use subquery with aggregate function firstly, then do JOIN by product_id.

Your group by column might get the wrong result, Because you need to add non-aggregate column in group by when you use aggregate function.

Query #1

SELECT p.product_id, p.title, p.description, p.specifications, p.price, p.discount, p.discount_type, p.color, p.hours, p.product_type, p.gender, p.img, p.vdo_intro, p.status, p.url, p.date_start, p.date_end, p.ip, p.product_page, p.product_code, p.brand,  p.color_url,                          
          c.category_id, c.category_url as urlcategory, c.parent,
          o.solds as solds
FROM (
     SELECT SUM(o.order_quantity) solds,o.product_id
     FROM order_detail o
     GROUP BY o.product_id   
) o
JOIN product p ON o.product_id = p.product_id                   
JOIN category_relations rcat ON rcat.product_id = p.product_id 
LEFT JOIN `category` c on rcat.category_id = c.category_id  
    WHERE p.gender = 'Hombre' AND p.status=1 AND p.date_end IS NULL 
 ORDER BY o.solds DESC LIMIT 12;

Query #2

SELECT p.product_id, p.title, p.description, p.specifications, p.price, p.discount, p.discount_type, p.color, p.hours, p.product_type, p.gender, p.img, p.vdo_intro, p.status, p.url, p.date_start, p.date_end, p.ip, p.product_page, p.product_code, p.brand, p.color_url,                       
          c.category_id, c.category_url as urlcategory, c.parent,
          v.visits
 FROM (
    SELECT SUM(v.total) as visits,v.product_id
    FROM `visits` v
    GROUP BY v.product_id

 ) v
 JOIN `product` p ON v.product_id = p.product_id
 JOIN category_relations rcat ON rcat.product_id = p.product_id 
LEFT JOIN `category` c on rcat.category_id = c.category_id                  
 WHERE p.gender = 'Hombre' AND p.status=1 AND p.date_end IS NULL  
 GROUP BY rcat.cat_relation_id
 ORDER BY v.visits
     DESC LIMIT 12;

View on DB Fiddle