FAQ Database Discussion Community


Grouping by date in groupconcat after concatenation

mysql,group-concat
I've got a list of stores in a store table, I've got a storecomment table as well which contains multiple comments for each store. They are linked via the storeID foreign key. This is a subquery for a much larger query to return multiple pieces of information into a single...

group_concat duplicate values

mysql,select,group-by,group-concat
I wrote two queries to concat values with different conditions, the results of two queries should be same,but I couldn't get correct result with query2.The two queries are as follows: query1: SELECT group_concat(concat(concat(concat(concat(concat(f.NAME, ';') , sgk.NAME),' ') ,cc.operator),' ')) as res_string FROM complex_check_anag cc,lnksinglechecktocomplexcheck lk,single_check_anag sgk,functionalci f ,lnkconfigurationitemtosinglecheck lkcg WHERE...

SQL Server Group By with Group Concat

sql-server,group-by,group-concat
I have a table like this Hospital Insurance PatientCount H1 I1 1 H1 I1 2 H2 I1 1 H2 I2 1 Want to group this table by insurance as, Hospital Insurance PatientCount H1,H2 I1 4 H2 I2 1 Tried using select stuff((select ', ' + Hospital from Insurances where (InsuranceName...

example GROUP_CONCAT

mysql,sql,group-concat
I have a table with two columns (case and subcase) and the following values: case subcase 1 0 2 1 3 1 4 0 5 0 6 4 I want to have a list of cases only having subcase(s) like: case list_of_subcase 1 2,3 4 6 5 0 Cases 2,3,and...

mysql multiple grouping on one table

mysql,group-by,subquery,group-concat
I have one table job_result CREATE TABLE job_result ( node varchar(20) DEFAULT NULL, jobId int(10) DEFAULT NULL, subResult int(1) DEFAULT NULL ) The table contains many nodes. Each node has many jobId. Each jobId has many subResults (usually less than 10). Some example data provided insert into job_result values ('A',...

group_concat in mysql with “case when ” conditions

mysql,case,concat,group-concat,.when
I wrote sql code in mysql environment to concat the data . But, I couldn't get the correct result , and I am confusing about what is wrong with my sql code. my sql code is as follows: SELECT case when cc.complex_check_id = cmt.comp_o_id then cc.status cstatus,sgk.status sgstatus,cc.NAME complex_check_name,cc.min min_flag,cmt.comp_t_name...

Determine age of items in group_concat MySQL

mysql,group-concat
Is it possible, in a query, to determine age from dates contained inside a group_concat? Perhaps combining the following? group_concat(children.age) TIMESTAMPDIFF(YEAR, age, CURDATE()) Or do I need to handle in PHP?...

Incorrect results when using GROUP_CONCAT with a where clause condition

mysql,sql,group-concat
I have a table like this: ID | GenEx | CodeName | Desc ---------------------------- 1 | Cipro | Dolvo | 2 | Ludavil | Ymir | 3 | Cipro | Alpha | My query is like this: SELECT GenEx, GROUP_CONCAT(CodeName) AS Code FROM Drugs D WHERE `CodeName` IN ('Alpha') GROUP...

How to use Group_Concat() to concat in MySql

mysql,concat,group-concat
I have table structure like this- Code Codelang Name 14 de David 14 en Michel 14 es John I want to show this table as- Code Name 14 [:de]David[:en]Michel[:es]John[:] Is it possible to do this using Group_Concat() or is there any other way to do this.?...

How to get mysql Group_Concat to work with additional CONCAT information?

php,mysql,concat,group-concat
I'm working on a web app with mysql involving a user role table where permission levels are stored as well as role ids and associated to usernames and their ids (redundancy to continue supporting legacy code). I want to be able to display every user's role as a comma seperated...

MySQL split on semicolon in a column to form a new row for each split

mysql,split,concat,group-concat
So I've been given legacy mysql db with data that looks like this: DATE | DATA 2015-04-27 | sample; sample2; sample3 2015-04-28 | sample1; sample4 I'm looking to create a new table that would store date/data pairs like this. DATE | DATA 2015-04-27 | sample 2015-04-27 | sample2 2015-04-27 |...

MySQL group_concat() month wise ordering

mysql,order,group-concat
I have a mysql table with date column named MonthYear which contains dates from March 2013 to Jan 2014 in yyyy-mm-dd format. I trying to group_concat() the that column as, select GROUP_CONCAT(DISTINCT CONCAT(' ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, ''%b %y'')=''', DATE_FORMAT(ms.MonthYear, '%b %y'), ''' THEN Count ELSE 0 END)) AS ''', DATE_FORMAT(ms.MonthYear,...

Correct way of using grunt's “bower_concat” and “angular-i18n”

angularjs,gruntjs,group-concat
If one is not using grunt's "concat" and "bower_concat", angular-i18n is used this way: <html ng-app> <head> ... <script src="angular.js"></script> <script src="i18n/angular-locale_de-de.js"></script> ... </head> </html> (According to here: https://docs.angularjs.org/guide/i18n) But... of course: I'm using concat and bower_concat. I'm using them this way: First I use bower_concat and create build/bower-concat.js Note:...

How to use MySQL group_concat() function in more than one column

mysql,group-concat
I have this query SELECT de.id as id_evento,de.evento, dp.id as id_participante, dp.nombre as participante,pn.participacion, pn.id as id_participacion FROM difusion_evento de,difusion_evento_participante dep,difusion_participante dp, difusion_participacion pn WHERE de.id=dep.difusion_evento_id AND dep.difusion_participante_id=dp.id AND dep.difusion_participacion_id=pn.id AND de.id=5 ORDER BY 3,6 And the results are: id_evento evento id_participante participante participacion id_participacion 5 5TO SEMINARIO PERMANENTE… 8...

MySQL group_concat twice in SubQuery DISTINCT

mysql,subquery,group-concat
I have table like this: title name 1 ADAM SMITH 1 JACK SMITH 1 ROBERT ABADI 2 JACK SMITH 2 JAMES ANDERSON 3 JACK SMITH When I call data with query looks like SELECT title, name, group_concat(name) as gname FROM sample GROUP BY title The result is: 1 ADAM SMITH...

Group multiple data in result on ms sql

sql-server,group-concat
I'm trying to group multiple results queries in one line but didn't find an other way than the following. But I'm not truly satisfied with this solution. Is there a more elegant solution for that? The situation a classic n:m mapping Table 1 - Basis Table 1 similar to this...

MySQL Update Row from Query

mysql,group-concat
I've got a complex (For me!) query that basically is grabbing the data from 2 tables then grouping them togeher (This bit works 100% as a SELECT query) But when I now need to update the database in another table, it won't work. I've got this: UPDATE exp_channel_data data, (...

How can I use GROUP_CONCAT in Rails?

sql,ruby-on-rails,oracle,group-concat
I have the following query which I want to use with ActiveRecord so that it can be translated in native ORACLE based query on production server. Right now I am using SQLITe. select c.name,co.code,GROUP_CONCAT(c.name) AS GroupedName from countries c INNER JOIN continents co on c.continent_code = co.code INNER JOIN event_locations...

Searching through the GROUP_CONCAT values in MYSQLI?

mysql,mysqli,group-concat
If i have 5 names returned in my group concat, is it somehow possible to further limit the returned table? If i for example only wish to see the firms with the name of "Assembly", and if i use that in an AND "AND cell_name = 'Assembly'" then my group...

Mysql GROUP_CONCAT is extremely slow

php,mysql,group-concat
I have a table in database with 450.000 rows. Why is this query extremely slow? Can I fix it? SELECT `ART_ARTICLE_NR`, `NAME`, `SUP_BRAND`, `PATH`, `CROSS_TYPE_NO`, GROUP_CONCAT(`CATEG` ORDER BY `STR_LEVEL` ASC SEPARATOR '>>') AS CATEG2 FROM TOF_ARTICLES2 LIMIT 9 ...

MySQL - GROUP_CONCAT if value is not a substring

mysql,group-by,group-concat
I have a column called "Permissions" in my table. The permissions are strings which can be: "r","w","x","rw","wx","rwx","xwr" etc. Please note the order of characters in the string is not fixed. I want to GROUP_CONCAT() on the "Permissions" column of my table. However this causes very large strings. Example: "r","wr","wx" group...

mysql self join with group_concat and without duplicates

mysql,duplicates,group-concat,self-join
I would like to get rid of duplicates in my DB. There can be several duplicates of one criterion, which are then grouped together. Let's say B is duplicate of A, and C is also duplicate of A then there should be a result like *id* | *duplicate* A |...

Mysql working with comma separated list - Junction table

mysql,group-concat,junction-table
I have a Junction table with ProductID and Accessory column: TABLE1 ProductID Accessory 1 2 1 3 2 1 2 4 2 5 3 4 1 5 2 It means that for the ProductID 2, it has the Accessory ProductIDs 1,4 and 5 ... and i have THE TABLE 2...

MySql group_concat behaviour when used with insert and a limit

mysql,group-concat
I'm a little confused here. I have table_a and table_b, and run the SQL below. table_a "id" "addedUser" "addedName" "1" "1" "james" "2" "1" "kirk" "3" "1" "lars" "4" "2" "michael" table_b "id" "userName" "userDisplay" "userFreinds" "1" "norman" "james" "james,kirk,lars" insert into table_b ( userName, userDisplay, userFreinds ) select 'norman',...

mysql select concat of one-to-many matches on left join

mysql,concat,group-concat
If have a table roots like: id | root and another extensions like id | order | extensionpre | extensionval | extensionpost How can I build a view like id | root | group_concat(concat(extensionpre, extensionval, extensionpost)) Right now I'm trying: SELECT id, root, GROUP_CONCAT (concat(extensionpre, extensionval, extensionpost)) FROM roots AS...

mysql request using join and group_concat

mysql,join,group-concat
I have three tables "names", "groups", "tasks" "names" | nid | name | | 1 | John | | 2 | Jim | | 3 | Jerry | "groups" | gid | nid | | 1 | 1 | | 1 | 2 | | 2 | 2 | |...

group_concat is not working in php

php,group-concat
I have following mysql table: table: bid id | compid | itemid | rate | year 1 | 1 | 1 | 2.0 | 2015 2 | 1 | 2 | 4.2 | 2015 3 | 1 | 3 | 2.3 | 2015 4 | 2 | 1 | 3.0...

How to get distinct values on GROUP_CONCAT using Google Big Query

distinct,google-bigquery,group-concat
I'm trying to get distinct values when using GROUP_CONCAT in BigQuery. I'll recreate the situation using a simpler, static example: EDIT: I've modified the example to represent better my real situation: 2 columns with group_concat which needs to be distinct: SELECT category, GROUP_CONCAT(id) as ids, GROUP_CONCAT(product) as products FROM (SELECT...