Questions tagged [group-concatenation]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
8
votes
2answers
5k views

Why set `group_concat_max_len` below the maximum?

MySQL 5.5.28 on Ubuntu 12.04 If the result is longer than group_concat_max_len then the result is gracelessly truncated. Currently I have a script that tries to check the required length ahead of ...
7
votes
1answer
12k views

Concatenate one-to-many field in single result?

Say I have the following query: SELECT * FROM AppDetails, AppTags WHERE AppDetails.AppID = '1' AND AppDetails.AppID = AppTags.AppID Which gives the following results: AppID AppName ...
6
votes
3answers
11k views

group_concat and group by together

I am trying to write a query using group_concat, concat and other functions, but I am getting stuck not able to join and group the data to represent them in 1 cell. Here's how my data is... table1 +...
5
votes
3answers
27k views

Concatenating rows into a single string query running for 5 hrs and counting

I have a table with 2.6m records. It looks like this: email prject_name rafael.nadal@xyz.com lab1 rafael.nadal@xyz.com lab2 rafael.nadal@xyz.com ...
5
votes
1answer
34k views

Multiple “GROUP_CONCAT”s with WHERE clause

In a table as id name type info 1 BMW car yes 2 Reno car no 3 IBM electronics no 4 Sony electronics yes 5 Mazda ...
4
votes
2answers
2k views

Query monthly having group_concat and group by need help

I’m stuck on a join tables query presenting data monthly involving group by and group_concat, and need a pointer badly. Here’s a simple client table. -------------------------------- id | Name 1 |...
4
votes
1answer
3k views

MySQL - GROUP_CONCAT returns duplicate data, can't use DISTINCT

I have a normalized database and I'm trying to return data from multiple tables using JOINs and GROUP_CONCAT. Problem: Rows are being duplicated with GROUP_CONCAT. I can't use DISTINCT because some ...
4
votes
1answer
1k views

When does a MySQL session end?

I have a stored procedure as described below. DROP PROCEDURE `GetVoteID`// CREATE DEFINER=`u1037413_manager`@`%` PROCEDURE `GetVoteID`(IN minimum_votes INT(11), IN current_round INT(11), INOUT ...
3
votes
1answer
8k views

Add a JOIN to GROUP_CONCAT()

I have a table of mostly-static objectives and another to track users' completions of those objectives. They can associate the completion with one or more records entered in another table and/or a ...
3
votes
1answer
71 views

Getting the query right?

CREATE TABLE `Action` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `created_By_Id` int(10) unsigned NOT NULL, `name` varchar(60) NOT NULL, `created_At` datetime NOT NULL DEFAULT ...
3
votes
1answer
20k views

Selecting multiple columns from multiple rows in one column (subquery?)

I'm working with MySQL 5.1.68. I have a situation where I'd like to select rows from another table into a column. I'd like to output this: id # name # citizen_name (multiple citizen names) 1 # The ...
2
votes
2answers
13k views

Getting repeated value in a group_concat only if ids are different

I have two tables, first the table Product: id|category_id --+----------- 1 | 12345 2 | 12345 3 | 12465 And then a table activity: id|prod_id|activity_type |description --+-------+----------------...
2
votes
2answers
2k views

COUNT(), GROUP BY and GROUP_CONCAT() together on MySQL

My question is quite simple. I just want to count events from each client and present them in a comma separated single row. I have this table: +----+----------------+ | id | event | +----+--...
2
votes
1answer
533 views

SQL ISO Standard for group concatenation

Is there an operator in the latest standard SQL:2011 (or earlier) that will handle group concatenation the likes of which we currently have to resort to these tricks for? GROUP_CONCAT in MySQL FOR ...
2
votes
1answer
3k views

Modify LISTAGG query for 10g

below mentioned code for cursor works only in Oracle 11g, as LISTAGG() function introduced starting version 11g. I need to adapt this code for Oracle 10g. Any help would be appreciated. SELECT ...
2
votes
2answers
383 views

How to handle nested groupings with possible null elements in one query

As you can see in this sql-fiddle, I have three tables: events, event_dates, and event_times. The original tables contain various other fields, which I omitted as they aren't relevant, I just need to ...
2
votes
2answers
1k views

MySQL GROUP_CONCAT Query, is there a better way?

I have the below Data / Query. Is this really the most efficient way of doing this? As it seems like a lot of work to get the result. I've tried reducing it / combining the parts but this is the ...
2
votes
1answer
1k views

Join two group_concat result

this is the query: $sql = "select pn.id, pn.purchased_number, pn.plan, pn.ring_to_group, count(rtg.id) as totalGroups, concat_ws(',', group_concat(rtg.group_name), group_concat(rtg....
2
votes
1answer
29 views

Problem filtering information in MySQL query

I've a little problem with a SQL query. The objective is to get an employee profile related to a role. Table EMPLOYEE (Louis,Mary,Christian): EmpId EmpName Table ROLE (Chef,Waiter,NULL): RoleID ...
2
votes
1answer
767 views

MySQL “Out of sort memory” error on a simple GROUP_CONCAT query

I have a (what it seems to me) simple query which fails to run on a simple table, the error is: Out of sort memory, consider increasing server sort buffer size These are the SQL statements which I'...
2
votes
0answers
372 views

Retrieve all product from one category (tree model)

I'm using MySQL, have categories in a tree model. One product belongs to only one (leaf) category. What I want to accomplish is, given one category (any, could or not be a leaf or a root), return ...
2
votes
3answers
3k views

Problem with mysql group_concat, complex query, not getting required result

I have 5 tables college trade district contact intake Their relationship looks like this I'm having problem with the contact table. In this table, cid and location are the primary key. It can have ...
1
vote
1answer
41 views

Mysql subquery not recognizing the subquery join table field

I have a three tables. The main table is the journeydata and next table is the alarm tables. The primary key for journeydata is journeydataID and its available as key in the alarm tables. Next in the ...
1
vote
1answer
671 views

Mysql Select recent 2 records for each group[user]

I have two tables users and login_track. Each user may or may not have login records in login_track table. I want to retrieve recent 2 login dates for each user. Table structures as below: users: ...
1
vote
2answers
8k views

MySQL - trying to combine `GROUP_CONCAT` with `COUNT()`

I am trying to combine GROUP_CONCAT with COUNT(), and failing. I have the following data: CREATE TABLE `tab1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `team` varchar(45) DEFAULT NULL, `...
1
vote
1answer
5k views

GROUP_CONCAT with ORDER BY , but results are not ordered

The following query works fine SELECT *, (SELECT GROUP_CONCAT(url SEPARATOR '$$' ) FROM project_photos WHERE project_id = projects.id ORDER BY priority) AS images FROM projects WHERE catID = 2 LIMIT ...
1
vote
1answer
829 views

GROUP_CONCAT without limit by WHERE

Buildings and Facilities have a many-to-many relation with a linking table of ids in between. I would like to select Buildings with specific Facilities, and also show each Building's other Facilities ...
1
vote
1answer
12k views

Using wm_concat to concatenate rows, but in an order determined by another column

Let's say I have 3 columns: p_id, description, order_by. I am trying to do the following: I would like to concatenate the description for all like p_id values. So we are talking a group by p_id ...
1
vote
0answers
27 views

MySQL: sum values with join and group concatenation

I've got the following tables: users: mysql [test]> SHOW CREATE TABLE users\G *************************** 1. row *************************** Table: users Create Table: CREATE TABLE `users` ...
1
vote
2answers
116 views

How to group same columns from same two tables, but from two different result sets and/or conditions

I am having troubles coming up with a query for something that should be quite simple. Initially, I had two tables of data that I generated joint result sets from; an events table, and a groups table ...
1
vote
1answer
216 views

MySQL Group Concat one cell when other cells also match

I'm trying to concatenate the content of a single cell when two cells from other colums also match. For example, say I have a table (including joins) that looks like this: +------------+--------------...
1
vote
1answer
922 views

MySQL sort data before using GROUP_CONCAT to concat it together?

I have a system that tracks several thousand objects in realtime and stores their locations in a database table. Rather than store their location every few seconds I simply store their location, ...
1
vote
0answers
183 views

Mysql trigger with group_concat

I'm trying to create insert and update triggers with SELECT GROUP_CONCAT request on one of the fields; This select query perfectly works, when I trying it in console, but trigger saves only first ...
1
vote
1answer
444 views

convert 1 row as column and concatenate the results to the corresponding column

I have 2 tables table 1 - apps ----------- id | name ----------- 1 | abc ----------- 2 | sdf ----------- 3 | dfg ----------- table 2 - contacts ------------------------ id | cnt_type | ...
1
vote
0answers
1k views

Duplicate GROUP_CONCAT values

Writing a plugin for Wordpress using a custom post type and an additional alias table to hold repetition of the post events. The problem comes with duplicate post meta values. wp_post_meta table: ...
1
vote
0answers
2k views

How can I join one to one to (many to one) without using group_concat

I have a table structure as follows: -- -- Table structure for table `emails` -- CREATE TABLE IF NOT EXISTS `emails` ( `ID` int(5) NOT NULL AUTO_INCREMENT, `email` varchar(255) COLLATE ...
0
votes
2answers
270 views

Use GROUP_CONCAT() to generate polygons in mysql

I have a table that has columns zone(name of the zone which is a string), longitude (decimal), latitude (decimal), logical order (integer). logical_order indicates the order in which the coordinates ...
0
votes
1answer
28 views

Group_concat on IF Select MYSQL

I have a query as below: SELECT o.order_id, c.customer_name, Group_concat(p.product_name, ' x', p.product_quantity ORDER BY receipt_order ASC SEPARATOR '<br>')...
0
votes
2answers
64 views

How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY

I wrote the following SQL query to group results and then sort results in each group by date: SELECT origin, provider, SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY departure_date ...
0
votes
2answers
95 views

How to merge records for query set

We have a question related to a DB query : CREATE TABLE `Action` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `emp_Id` int(10) unsigned NOT NULL, `name` varchar(60) NOT NULL, `...
0
votes
1answer
16 views

count and group_concat

I have data in a table like so: +---------+----------------------------------------------------+ | videoid | ips | +---------+---------------------------...
0
votes
3answers
9k views

How to concat one column of multiple rows (group by clause used in query)

I had some issue while populating some data as per my requirements and that is as follows.. Table data: --------------------------------------------- EMPID PROJ_TYPE HOURS ST_DATE ED_DATE ----...
0
votes
1answer
36 views

Group stored procedures in oracle [closed]

We are writing tons of oracle stored procedures to handle our business logic. We would like to do a physical grouping for better maintenance and understanding. I can see package is helpful for ...
0
votes
1answer
217 views

mysql not using index w/ group_concat

I've got a simple temp table that I'm creating below CREATE TEMPORARY TABLE IF NOT EXISTS Helper0910912001524518044820 ( usergroupID INT, StoreID INT , INDEX st (`usergroupID`) ) ...
0
votes
1answer
252 views

Optimize multiple group_concat

Just wondering if this query could be optimized or if sub-queries would be better? select t.entry_id, t.title, d.field_id_249 as unique_id, d.field_id_577 as keywords, d.field_id_681 AS video, ...
0
votes
1answer
353 views

Why group_concat and SQL_CALC_FOUND_ROWS do not work with each other?

I have following query which contain group_concat and SQL_CALC_FOUND_ROWS together as I want result + total no. of records present in it. SELECT SQL_CALC_FOUND_ROWS GROUP_CONCAT(ca.id) FROM ...
0
votes
1answer
118 views

MySQL, would like to get both a field and a count inside GROUP_CONCAT

I can get a list of statuses that a tracking ticket has had, as well as the id of those statuses, as follows: SELECT t.id, t.name, GROUP_CONCAT( CONCAT(s.id, ':', s.name) ORDER BY s.id) AS ...
0
votes
1answer
54 views

MySQL group concatenation - creation of insert statement on temp table

I have a temporary table TEST_GROUP, from where I'm working its data to fill another one. From this table, giving the example in the picture, my expected result is to group these three rows, so I can ...
0
votes
1answer
832 views

Select post and concatenating all categories it belongs to

I have 3 DB tables POSTS +----------+----------+----------+ | id | title | content | +----------+----------+----------+ CATEGORIES +----------+----------+ | id | title | +---------...
-1
votes
1answer
433 views

Order by a different field on a GROUP_CONCAT

Given the following script: SELECT category AS MODULE, acl_roles.name as ROLE_NAME ,GROUP_CONCAT( CASE aclaccess WHEN 89 THEN 'E' WHEN 0 THEN 'N' ...