How to group concat columns with drag and drop builder?
26 December, 2013
Dear support,
I would like to group on a column on a related table. Normally I would like to use a group_concat method of Mysql where I group on the first table primary key.
F.e.: SELECT group_concat(category_name) from user INNER join category ON user.id = category.user_id GROUP BY user.id
See mysql documentation: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
Is there a possibility to get the same results within the drag & drop query builder?
We're using the new 7.0 version.
Thanks
I would like to group on a column on a related table. Normally I would like to use a group_concat method of Mysql where I group on the first table primary key.
F.e.: SELECT group_concat(category_name) from user INNER join category ON user.id = category.user_id GROUP BY user.id
See mysql documentation: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
Is there a possibility to get the same results within the drag & drop query builder?
We're using the new 7.0 version.
Thanks
Hi,
If you are able to get the results using Freehand SQL, then you should be able to simply create a free-hand sql calc field in your report (see below):
We do also include an analytic function to concatenate columns, however we do not allow grouping on that column after the function has been applied.
Please let me know how you go with this.
Regards,
David
If you are able to get the results using Freehand SQL, then you should be able to simply create a free-hand sql calc field in your report (see below):
We do also include an analytic function to concatenate columns, however we do not allow grouping on that column after the function has been applied.
Please let me know how you go with this.
Regards,
David
This doesn't seems to work because the freehand SQL doesn't allow you to use aggregate fields for calculated fields (as stated in the box in the screenshot).
Hi,
That is more of a warning. You should be able to use aggregate functions with free-hand SQL. We just want to ensure that users are aware of what aggregations can be applied to their fields, since YF is unable to validate this. We simply let users do what they want to do, and hope they do it right ;).
Maybe you could try building a calc field and then apply the aggregate once you drag it into the columns?
What happens if you actually try using your freehand SQL?
Regards,
David
That is more of a warning. You should be able to use aggregate functions with free-hand SQL. We just want to ensure that users are aware of what aggregations can be applied to their fields, since YF is unable to validate this. We simply let users do what they want to do, and hope they do it right ;).
Maybe you could try building a calc field and then apply the aggregate once you drag it into the columns?
What happens if you actually try using your freehand SQL?
Regards,
David