Web lists-archives.com

Re: Why does a group_concat on a join change aggregate values?




Hi Paul,
On 5/7/2015 10:17 AM, Paul Halliday wrote:
Fighting a bit with this one...

If I do something like (pseudo):

SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types FROM tbl1

returns something like:

n  c_types
1  t9

when I add a left join though:

SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types,
GROUP_CONCAT(two.types) AS d_types FROM tbl1 LEFT JOIN tbl2 AS two ON
tbl1.id = tbl2.id

returns something like:

val  c_types          d_types
3    t9,t9,t9         a2,a3,a9

I can have as many group_concats against the same table with varying
results and they don't affect COUNT() but once I do that JOIN things start
to fall apart.

What is happening behind the scenes?

Thanks!

Here's a simple test. Change the query to no longer have the aggregate functions, then start counting rows by hand.

SELECT
  val AS n
, types AS c_types
, two.types AS d_types
FROM tbl1
LEFT JOIN tbl2 AS two
  ON tbl1.id = tbl2.id

The other thing that springs to mind is that you lack a GROUP BY in your query. It isn't required but they can often help get you to the correct answer.

Best regards,

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql