Questions tagged [group-concatenation]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
12
votes
2answers
11k 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
3answers
17k 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 +...
7
votes
1answer
13k 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
1answer
42k 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 ...
5
votes
3answers
32k 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 ...
4
votes
2answers
4k 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. Here’s a simple client table (DDL and DML at the bottom of this post): id | Name 1 | Sony 2 | Toshiba 3 ...
4
votes
1answer
15k 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 ...
4
votes
1answer
5k 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
4answers
370 views

How to use GROUP BY in a way concatenates data in one column, but filters for specific data in another

I am running SQL Server 2014 I have a table that looks like this: ID | Name | AddressType | Address | Features ======================================================== 1 | Bob | Home ...
3
votes
2answers
3k 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 | +----+--...
3
votes
2answers
15k 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 --+-------+----------------...
3
votes
1answer
79 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
2answers
4k 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 ...
3
votes
1answer
22k 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 ...
3
votes
3answers
4k 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 ...
2
votes
1answer
728 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
2answers
14k 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, `...
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
568 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
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
42 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
5k 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
436 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 ...
1
vote
1answer
9k 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
1k 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
1answer
70 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
3answers
71 views

Fill missing data points in `GROUP_CONCAT`

I need to generate data to build some charts. The current results have missing data points and I'd like them to fill them with 0's. Data is stored in MySQL 8. Simplified data sample & query fiddle ...
1
vote
1answer
1k 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
13k 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
32 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
1k 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
407 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
2k 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
255 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
789 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
535 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
2answers
27 views

LIMIT the number of rows in GROUP BY and GROUP_CONCAT with ORDER

In this sqlfiddle, CREATE TABLE t1 ( id int(11) unsigned NOT NULL, val int(11) unsigned NOT NULL, rank mediumint(7) unsigned, INDEX(id), INDEX(rank), PRIMARY KEY(id,val) ) ENGINE=InnoDB DEFAULT ...
0
votes
2answers
29 views

Group_concat with joining multiple tables

I have the following scheme users: researches: researchyear: uid | name rid| titleAr rid | uid | ---+-------- ---+------------ -----...
0
votes
1answer
514 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
217 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
100 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
34 views

How can I group multiple records as a single .csv string line?

I have a relation where a User has multiple dogs (as many as 15) but each dog is contained in a single row in the table, and they all have a userId in common. For example, Table `dogs`: | User | ...
0
votes
1answer
29 views

count and group_concat

I have data in a table like so: +---------+----------------------------------------------------+ | videoid | ips | +---------+---------------------------...
0
votes
3answers
18k 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
39 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
444 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
357 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, ...