Questions tagged [group-concatenation]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
16 views

count and group_concat

I have data in a table like so: +---------+----------------------------------------------------+ | videoid | ips | +---------+---------------------------...
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` ...
0
votes
2answers
48 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 ...
1
vote
2answers
71 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 ...
0
votes
1answer
77 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 ...
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 ...
3
votes
1answer
67 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 ...
0
votes
3answers
8k 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 ----...
1
vote
1answer
682 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 ...
0
votes
2answers
215 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
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 ...
-1
votes
1answer
391 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' ...
2
votes
2answers
1k 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 | +----+--...
0
votes
1answer
184 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`) ) ...
2
votes
1answer
613 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'...
8
votes
2answers
4k 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 ...
1
vote
1answer
169 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: +------------+--------------...
0
votes
2answers
92 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, `...
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 |...
1
vote
1answer
554 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
846 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, ...
0
votes
1answer
232 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, ...
3
votes
1answer
7k 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 ...
2
votes
0answers
327 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 ...
5
votes
3answers
24k 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 ...
2
votes
2answers
356 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 ...
1
vote
0answers
167 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 ...
2
votes
2answers
972 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 ...
6
votes
3answers
10k 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 +...
1
vote
2answers
7k 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
395 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 | ...
4
votes
1answer
953 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 ...
2
votes
3answers
2k 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
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 ...
0
votes
1answer
52 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
341 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
814 views

Select post and concatenating all categories it belongs to

I have 3 DB tables POSTS +----------+----------+----------+ | id | title | content | +----------+----------+----------+ CATEGORIES +----------+----------+ | id | title | +---------...
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 ...
3
votes
1answer
19k 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 ...
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 ...
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
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 ...
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....
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 ...
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 --+-------+----------------...
5
votes
1answer
33k 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 ...
2
votes
1answer
503 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 ...
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 ...