Questions tagged [group-concatenation]
The group-concatenation tag has no usage guidance.
65
questions
2
votes
3
answers
48
views
IF(GROUPING(id)=1, "Total", name) returns the same wrong "name" across all rows, except for the total case
I'm using MySQL 8.0 and performing select query with totals and sub-totals, hence it's about with rollup statement.
The problem is that the expression IF(GROUPING(id)=1, "Total revenue", ...
- 21
1
vote
1
answer
26
views
How can I get non-primary key fields in a GROUP_CONCAT that goes through a linked table?
I have two MySQL tables, users and supervisors. In this simplified example, users has just an ID and a Username, and supervisors is a table that links two users together.
I would like to construct a ...
- 13
0
votes
1
answer
55
views
Products multiple variants
My query looks like:
select p.id, p.name, vo.value, v.name
from products p
join products_variant_options pvo on pvo.product_id = p.id
join variant_options vo on vo.id = pvo.variant_option_id
join ...
- 123
0
votes
1
answer
245
views
Multiple GROUP_CONCAT statements within single MySQL Query
I have a table where each record has a one to many relationship with 3 other tables (with further one to one branching) leading to many rows for each main record with many columns of duplicate ...
- 103
1
vote
1
answer
39
views
When I use group_concat on two fields, can I count on the two concatenations having the same order?
If I have a table like this:
bagid fruit color
----- ----- -----
1 apple red
1 banana yellow
and I select bagid,GROUP_CONCAT(fruit),GROUP_CONCAT(color) grouped by bagid, can I ...
- 113
0
votes
1
answer
322
views
Group concat up to a maximum number of rows in SQLite
I want to group concat several rows, but only up to maximum number of x rows.
Here is the code without the maximum
create table if not exists csvs (
id integer,
csv text
);
insert into csvs (id, ...
- 113
1
vote
2
answers
3k
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 ...
- 4,429
3
votes
5
answers
6k
views
How to use GROUP BY in a way that 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 ...
- 137
0
votes
2
answers
1k
views
Group_concat with joining multiple tables
I have the following scheme
users: researches: researchyear:
uid | name rid| titleAr rid | uid |
---+-------- ---+------------ -----...
- 113
1
vote
3
answers
560
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 ...
- 143
0
votes
1
answer
235
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 | ...
- 231
-2
votes
2
answers
5k
views
MySQL Join two tables with comma separated values
I've got two tables as below:
CARLINE
option_value
option_name
tipologia_auto_1
Citycar
tipologia_auto_2
Berlina
tipologia_auto_3
SUV
Hobby_1
football
Hobby_2
Basket
Datimarketing (is the ...
0
votes
1
answer
918
views
What is the best way to handle a join / group_concat query in MySQL
I am looking for some feedback regarding the best way to handle the following (simplified for this example) situation.
I have 3 tables: tasks, assignees, and users
---tasks----
id, task, completed
---...
- 101
0
votes
1
answer
490
views
Concatenate in MS Access - how to omit space and comma if first value is missing?
I am concatenating 2 values in a MS Access Report: "Method" and "Experiment" and they are to be separated by a comma (,).
I have figured out how to concatenate succesfully when the second value is ...
- 13
0
votes
1
answer
208
views
Dynamic Pivot Table syntax error
What's wrong with this Dynamic Pivot table query? I'm staring myself blind on this.
mysql> SET @sql = NULL;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT
-> GROUP_CONCAT(DISTINCT
...
- 101
0
votes
1
answer
948
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>')...
- 145
2
votes
1
answer
60
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
...
- 23
0
votes
1
answer
34
views
count and group_concat
I have data in a table like so:
+---------+----------------------------------------------------+
| videoid | ips |
+---------+---------------------------...
- 1
1
vote
0
answers
39
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` ...
- 459
0
votes
2
answers
821
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
...
- 45
1
vote
2
answers
2k
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 ...
- 111
0
votes
1
answer
794
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
...
- 105
1
vote
1
answer
104
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 ...
- 227
3
votes
1
answer
91
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 ...
- 229
0
votes
3
answers
19k
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
----...
- 571
1
vote
1
answer
2k
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 ...
- 115
0
votes
2
answers
767
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 ...
- 167
0
votes
1
answer
48
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 ...
- 103
-1
votes
1
answer
1k
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'
...
- 109
3
votes
2
answers
4k
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 |
+----+--...
- 165
0
votes
1
answer
712
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`)
) ...
- 149
2
votes
1
answer
6k
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'...
- 121
13
votes
2
answers
16k
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,220
1
vote
1
answer
636
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:
+------------+--------------...
- 13
0
votes
2
answers
123
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,
`...
- 229
4
votes
2
answers
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 ...
- 43
1
vote
1
answer
2k
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:
...
- 13
2
votes
1
answer
3k
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, ...
- 23
0
votes
1
answer
428
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,
...
- 101
4
votes
1
answer
20k
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 ...
- 173
2
votes
0
answers
579
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 ...
- 121
5
votes
3
answers
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 ...
- 59
2
votes
2
answers
827
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 ...
- 199
1
vote
0
answers
330
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 ...
- 111
3
votes
2
answers
7k
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,046
8
votes
3
answers
21k
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
+...
- 83
2
votes
2
answers
18k
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,046
1
vote
1
answer
996
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 | ...
- 13
4
votes
1
answer
2k
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
3
answers
6k
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 ...
- 33