Different result on count(*) when do left join

by

I have 2 different tables one is product and other count number of quantity in second table.
Count(*) gives different result when use left join condition and group by

LEFT JOIN ON and GROUP BY is different below is the query.

SELECT LEFT( substring_index(dp_ventes_par_segments.ref_article,"https://stackoverflow.com/",-1),LOCATE('-', substring_index(dp_ventes_par_segments.ref_article,"https://stackoverflow.com/",-1)) - 1)  as r,

COUNT(*) 
FROM `dp_ventes_par_segments`  

LEFT JOIN dp_ventes_qty 
ON dp_ventes_par_segments.ref_article=dp_ventes_qty.dp_ventes_id 

GROUP BY 
LEFT( substring_index(dp_ventes_par_segments.ref_article,"https://stackoverflow.com/",-1),LOCATE('-', substring_index(dp_ventes_par_segments.ref_article,"https://stackoverflow.com/",-1)) - 1)

dp_ventes_par_segments

ref_article
P-00493/ANY-C2-O
P-00492/ANY-C4-O

dp_ventes_qty

dp_ventes_id date
P-00493/ANY-C2-O 2023-10-01
P-00493/ANY-C2-O 2023-10-01
P-00493/ANY-C2-O 2023-10-01
P-00493/ANY-C4-O 2023-10-01

table dp_ventes_par_segments.ref_article and dp_ventes_qty.dp_ventes_id data contents like below

P-00493/ANY-C2-O
P-00492/ANY-C4-O

And Result Is wants below:

ANY - 2

Count ANY is 2

  • When you use LEFT JOIN you get rows in the first table that have no match in the second table. These will be counted. Use INNER JOIN to keep them out.

    – 




  • BTW, you can use GROUP BY r instead of repeating the expression.

    – 

  • If the second table has a quantity column, maybe you just want to select that instead of using COUNT(*)?

    – 

  • If you’re using MySQL 8.x you can use REGEXP_SUBSTR() instead of that complicated combination of functions.

    – 

  • Please add sample data as text for both tables in tabular form. as is I can’t figure put where the elements P-00493/ANY-C2-O live.

    – 




Try this

SELECT
  LEFT(SUBSTRING_INDEX(dp_ventes_par_segments.ref_article, "https://stackoverflow.com/", -1),
       LOCATE('-', SUBSTRING_INDEX(dp_ventes_par_segments.ref_article, "https://stackoverflow.com/", -1)) - 1) AS r,
  COUNT(*) AS count_result
FROM
  dp_ventes_par_segments
LEFT JOIN
  dp_ventes_qty ON dp_ventes_par_segments.ref_article = dp_ventes_qty.dp_ventes_id
GROUP BY
  r;

You can also use INNER JOIN if you only want to include rows with matching values in both tables.

Leave a Comment