Re: Why does a group_concat on a join change aggregate values?
On 2015/05/07 19:42, Paul Halliday wrote: Should have showed the whole thing. Take a look here (click image to see full output): http://www.pintumbler.org/tmp I don't see why this worries you. Joining often increases variation. Indeed, if in some case an inner join never did, maybe the joined tables are needlessly separate. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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 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 >> 3t9,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/
Re: Why does a group_concat on a join change aggregate values?
First, have you tried GROUP_CONCAT(DISTINCT types) ? Second I see my counts rise just as my group_concat() terms when I do something similar to what you're talking about. Also, here: val c_types d_types 3t9,t9,t9 a2,a3,a9 Your column headers don't seem to match your query. I don't see the count "n" being output at all in the example. Are you sure you're looking at count() and not something else? Count should count all non-null instances of foo (or count(*) works too). Thanks, Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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 3t9,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
Why does a group_concat on a join change aggregate values?
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 3t9,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! -- Paul Halliday http://www.pintumbler.org/
