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
Re: create_time
On 2015-05-07, at 07:17, mysql-digest-h...@lists.mysql.com wrote: I have, however, also had Martin's experience where create_time seemed improbable. Sigh. I have the same thought every evening, when I look over all the things I planned to do during the day... Compared to sitting in an office making stereotypical remarks about mankind, farming is breathtakingly exciting. I grant that there are days when you might spend hours in a tractor cab, listening to talk show rant or gabbing on your cell phone while the tractor drives itself. But the second you quit paying attention to what’s going on, or almost slumber off to sleep in boredom, bells and whistles are likely to start clamoring away, indicating a loose belt or a broken pin or a plugged up auger or the embarrassing fact that you just plowed half way through the township road bordering your field. -- Gene Logsdon Jan Steinman, EcoReality Co-op -- 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 shawn.l.gr...@oracle.com 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/
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/
