Web lists-archives.com

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




Should have showed the whole thing. Take a look here (click image to see
full output):

http://www.pintumbler.org/tmp

On Thu, May 7, 2015 at 4:11 PM, shawn l.green <shawn.l.green@xxxxxxxxxx>
wrote:

> 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
>
>


-- 
Paul Halliday
http://www.pintumbler.org/