1,351 questions
0
votes
0
answers
23
views
Retrieve data from multiple tables with multiple one to many relationship [duplicate]
I have 4 tables.
project
--------------------------------------
| project_id | name | client_id |
--------------------------------------
| 1 | Make Safe | 12 |
| 2 | ...
- 13
2
votes
1
answer
70
views
How to GROUP_CONCAT row values, but break groups by an arbitrary "gap" flag?
Let's say I have an existing SQL table (which I am given, I have no control over it) describing a list of connections between cities, with a column flagging any gaps. Here's a simplified example:
...
- 353
2
votes
0
answers
50
views
How MySQL group_concat works on shared hosting
I have the following query
SELECT
Dpn.Department_ID,
Dpn.Department_Name,
Dpn.Department_Icon,
Dph.department_info_header,
GROUP_CONCAT(Dpi.department_info_service SEPARATOR ', ') ...
- 77
0
votes
0
answers
23
views
Summation of total amount using criteria builder and group concat
In my problem, I want to show the id and this particular id related key-value pair (x: date, y: sum of total price). But I don't get this result.
I am using sum, concat and group concat in ...
2
votes
1
answer
114
views
How to sort values in group_concat()
I tried to sort a group_concat list but keeping getting error. Below is what I've tried. If someone can please help.
eg.
customer order_id status
ABC 1234 1
ABC 1235 5
DEF ...
- 153
1
vote
0
answers
89
views
Group Concat Distinct creating new row for each combination? - Tableau Custom SQL
I am having an issue with my Tableau report where group concat is not working as intended. My table uses custom SQL and grabs from a BI connector sending information from MongoDB.
Let's say the ...
- 11
0
votes
1
answer
39
views
How to use GROUP_CONCAT where only the sequential duplicates get dropped
My trafficlog table looks like this:
intLogID
strSessionID
strPage
1
e3a8240b39
./
2
e3a8240b39
./about
3
e3a8240b39
./contact
4
5accab7da9
./
5
e3a8240b39
./contact
6
e3a8240b39
./about
7
71ee2ea4fe
....
- 1,103
0
votes
1
answer
24
views
Mysql: Concatenate specific number of rows per result
I have a table with 400K records with single column with numbers. I need result set concatenated by 5000 records as strings.
Table DDL:
create table temp_table (entity_id INT NOT NULL);
Example insert ...
- 77
-1
votes
1
answer
70
views
SQLite query to chunk group_concat into groups that maximize a length constraint?
I have data in a SQLite table that I'd like to process in "chunks" that include concatenated fields of multiple rows up to an overall limit of 10,000 chars per chunk. I can run queries ...
- 53
-1
votes
1
answer
48
views
mysql select list name join on find_in_set list id [duplicate]
list name with join from other table
t1
id orderid items
1 1001 1,2,5
2 1002 5,3
3 1003 2,4
t2
id name
1 item1
2 item2
3 item3
4 item4
5 item5
Need to result ...
1
vote
2
answers
115
views
GROUP BY and GROUP_CONCAT for calculating competitions ranking [closed]
I would like some help with a problem that has had stumped me for two days.
I have 'results' table like this:
result_id
competition_id
competitor_id
competitor_ranking
1
1
1
0.1
2
1
2
0.4
3
1
3
0.2
4
...
- 21
0
votes
3
answers
134
views
MySQL: wrong sorting order in a query with ORDER BY + GROUP BY + GROUP_CONCAT() + COUNT(*) OVER() window function
Apparently, sorting is somehow applied incorrectly in mysql 8 (checked on 8.0.33 to 8.0.35) when query has ORDER BY + GROUP BY + GROUP_CONCAT() + COUNT(*) OVER() window function. See test case below (...
- 3,372
0
votes
0
answers
219
views
What's the alternative of Listagg in Netezza?
SELECT ID FROM TABLE_A WHERE DATE >= CURRENT_DATE;
The output for the above query will be
001
002
003
I need the output as 001,002,003. I am trying below in Netezza but GROUP_CONCAT is not ...
- 81
1
vote
3
answers
67
views
Creating a ratio between variables in a Stacked dataframe grouped by some Columns
I have a df as follows:
df_in
G1 G2 TPE QC
A S1 td 2
A S1 ts 4
A S2 td 6
A ...
- 884
0
votes
1
answer
35
views
MySQL selecting specific entries from joined table
I have a table with users' subscriptions to a specific service, billed monthly.
There is also a table of 'suspensions' - with 'from' and 'to' dates to store information about user's temporary ...
0
votes
1
answer
4k
views
How to use CONCAT? [duplicate]
I'm a beginner at SQL and I'm struggling to understand how to use the CONCAT function. Can someone provide some guidance or resources that can help me properly learn how to use this function ...
- 1
0
votes
1
answer
107
views
Two different concatenates based on the same table
I try to design a mysql query to get results from a Greek movie database like: Title, concatenated Directors and concatenated awarded people. So, I use:
SELECT
`MT_films`.`ID_films` AS `ID_films`...
1
vote
1
answer
42
views
Concatenate rows in one table as one column in different table MYSQL
I think fundamentally, I am not understanding how to accomplish joining 2 tables together using a related table, I need help.
I have one table called 'formoptionslist' it has 3 columns (id, name, ...
- 346
0
votes
1
answer
350
views
mariadb returns null values for join with group_concat after certain row count
Locally MariaDB 10.5
Server upgrade from MariaDB 10.3 -> 10.5 (server is master slave replication)
I have huge query with many joins and almost each has group_concat.
To simplify the problem - it ...
- 939
-1
votes
2
answers
44
views
Optimizing MySQL query with group_concat
I have an old PHP5 MySQL webapp whose code had to be adapted to PHP8/MySQL14.14
There's a view, querying few tables and based on that code:
(SELECT `db_demandes`.`bdd`.`id`
AS `Id`,
`...
-1
votes
1
answer
35
views
how to get description on column have multiple id in array to get the detail id mysql [duplicate]
I have a table profile look like this
table_1
reg_id
reg_name
reg_colour
1
John
2,3
2
Doe
1,3
3
Jane
table_2
id_colour
colour
reg_num
1
black
2,3
2
white
1,3
3
green
i want to join those column ...
0
votes
1
answer
1k
views
Denodo column DISTINCT values concatenation
I'm looking for ways to concatenate two or more row values using the "|" separator. I found and used the GROUP_CONCAT() function, but it doesn't work for me using the "DISTINCT" ...
- 922
0
votes
1
answer
62
views
mysql query results on 1 row with GROUP_CONCAT?
I'm not an experienced programmer so please go easy with me! And I'm not sure if what I want is possible. I may need to provide some more info.
We have a simple ticket system built in a Joomla website ...
- 1
-3
votes
1
answer
31
views
MySQL: WHERE in a GROUP_CONCAT clause
I've the following table:
table
+--------------------+-------+
| id | name | age |
+--------------------+-------+
| 1 | client1 | 10 |
| 2 | client2 | 20 |
| 3 | ...
- 1,925
1
vote
1
answer
48
views
Group_CONCAT CASE multiply outputs
I have an SQL query with this line:
GROUP_CONCAT(CASE WHEN t3.ship=1 AND t4.item=0 THEN t2.item_name END ORDER BY item_id SEPARATOR '<br>') `My Item List`
Output Now: Lamp
It works perfectly, ...
- 15
0
votes
1
answer
296
views
SQL GROUP_CONCAT without GROUP BY?
I have 4 tables
Main table holding the licence data called licence, for sake of example we care for 2 columns licence_id and owner_id
+------------+----------------+
| licence_id | owner_id |
+-...
- 1,182
3
votes
2
answers
183
views
How to join 3 tables with group_concat
I am using Cubecart for a customer website, and they have used comma-separated values to associate certain option IDs to products. I need to create a custom table which gets all this information out ...
- 1,254
0
votes
1
answer
2k
views
Why is GROUP_CONCAT not ordering by the specified column in Impala?
I'm trying to use the GROUP_CONCAT function in Impala and I want to order the results by one of the columns. Here's what I have so far:
SELECT column1, column2, GROUP_CONCAT(column3 ORDER BY column3 ...
0
votes
1
answer
2k
views
Alternative of order by in group_concat() Impala
I need to order text data inside group_concat(). The problem is that a different sequence is returned each time.
I tried sorting in a subquery and then using group_concat(), but it still returns a ...
0
votes
0
answers
53
views
how to set group_concat_max_len with pandas
In my sql, I have group_concat, but data will be cuted by GROUP_CONCAT(). So i set group_concat_max_len in my code like this below
engine = create_engine(xxx)
with engine.connect() as con:
with ...
- 25
-1
votes
1
answer
69
views
mysql group_concat problem: ISNULL replaces null values but in the incorrect order
When using GROUP_CONCAT, the ORDER BY clause appears to works correctly. However, when replacing NULL values using ISNULL or COALESCE, although the actual values are in order, the NULL values all end ...
- 1,007
0
votes
1
answer
29
views
Group_Concat function does not show all data in SQL search
I have a problem that I can't solve with a search in a database. I have a sentence that works perfectly and has been working for a while, which is the following.
return await conn
.query(
...
0
votes
0
answers
30
views
not preserving the sequence after the query is executed in mysql
I am using a query to insert the records in a table attendance_student but when the query executed it do not preserve the sequence in att_punch. preserve means as in the query 'ss' is coming at the ...
- 67
1
vote
1
answer
52
views
getting invalid use of group function error in query
I am trying to convert the data from student_attendance_daily on the basis of class_id and month id but I am getting error invalid use of group function
I cannot use JSON_ARRAYAGG because i am using ...
- 67
2
votes
1
answer
206
views
How to join two or more equal consecutive values of column into one in SQLite?
I have this SQL query:
SELECT
strftime('%Y-%m-%d', start_time) as day, group_concat(event_id, ' | ') as events
FROM
events_table
WHERE
start_time BETWEEN '2022-01-01 00:00:00' and '2022-...
- 333
-2
votes
1
answer
52
views
my query won't run, and I think the query is correct, anyone knows what I did wrong?
SELECT
usertype CONCAT(start_station_name ,"to", end_station_name) AS route,
COUNT (*) AS num_trips,
ROUND(AVG(cast(tripduration as int64/60),2) AS duration
FROM bigquery-public-data....
- 1
0
votes
1
answer
199
views
Replace with array from a SELECT GROUP_CONCAT
I am trying to do a str_replace without success.. for some reason the data from MySql isn't working inside the str_replace function...
Code to bring all strings which will be used to replace the ...
- 478
1
vote
1
answer
53
views
MySQL count group_concat using case
I have students work system. Each student can submit multiple assignments, and the assignments are either written or practical. For each student, I need to know who has ONLY submitted practical work, ...
- 1,254
1
vote
1
answer
123
views
write query to get expected output using mysql
table name: transactions
customer
debit
credit
a
70
50
a
100
20
a
20
60
b
100
20
b
40
80
b
10
30
c
100
200
c
100
30
c
80
90
d
100
200
d
90
30
d
80
90
e
100
100
e
100
30
e
80
90
check who have positive ...
- 19
1
vote
1
answer
172
views
Do i need add INDEX in mysql for GROUP_CONCAT?
for example
GROUP_CONCAT(scpc.spage_t SEPARATOR '|') AS compl_titles,
GROUP_CONCAT(scpc.spage_sku SEPARATOR '|') AS compl_skus,
Do i need to add INDEXES for spage_t and spage_sku for speed up MYSQL?
0
votes
0
answers
52
views
MySQL combining multiple columns with GROUP_CONCAT, per row
I have some objects which have duplicates. One table stores the objects (the 'primaries'); another stores the one-to-many relation of objects to their duplicates (the 'secondaries'):
Objects
-------
`...
- 17
1
vote
1
answer
83
views
MySQL view with row values concatenated, ordered and grouped in a column
I have a table of item reviews that looks like this:
**ReviewID** **ReviewerID** **ReviewText** **Category** **ItemID** **date_time**
01 1212 good product Mobile ...
- 723
0
votes
1
answer
413
views
Create a table with column names dynamically retrieved from a single-column query result
I have a select query that returns a single column. Is there a way in sqlite to create a new table using the results as column names?
I tried query but it did not work.
CREATE TABLE newTable (SELECT ...
- 130
-3
votes
1
answer
181
views
GROUP_CONCAT for multiple columns
Table:
CREATE TABLE cities
(
`year` INTEGER,
`job` TEXT,
`well` TEXT
);
INSERT INTO cities (`year`, `job`, `well`)
VALUES ('2001', 'A', 'G1'),
('2001', 'S', 'G1'),
('2001',...
- 1
2
votes
0
answers
156
views
Mysql GROUP_CONCAT incomplete, well under group_concat_max_len
Issue is shown query below.
The table has a column value_mean which is a float.
The issue is the last GROUP_CONCAT, it cut off the last 2 characters. Why?
MariaDB [testdb]> SELECT
a.value_mean,...
- 21
0
votes
1
answer
178
views
Impala SQL group_contact() increments by month
I have a data set which contains information on several individuals by month, such as:
id
date
1
201901
1
201902
2
202004
3
202101
3
202102
3
202103
I want to create a new variable that includes all ...
-2
votes
2
answers
78
views
UPDATE statement using GROUP_CONCAT()
I'm attempting to create a single query that UPDDATES another table where the SUBQUERY/ DERIVED-QUERY uses two functions, GROUP BY and GROUP_CONCAT().
I'm able to get my desired output if I utilize a ...
- 19
1
vote
1
answer
28
views
Using group_concat to remove duplicates with linking tables
I am building a library system using MySQL. The database includes tables for the music, composer and arranger. These are linked through linking tables. One piece of music might have multiple composers ...
- 65
0
votes
1
answer
100
views
SQLite query JOIN , CONCAT, subquery
I have been trying to use LEFT OUTER JOIN, GROUP BY and (failing) to use the CONCAT || function to get the maximum score for the best book of the decade but have had no luck finding out when two books ...
- 21
0
votes
0
answers
31
views
group some records and Ignore some differences in grouping records in mysql (PROBLEM IN GROUPING)
I want to group my records by the time they are modified. (time means the minute)
A screenshot from my table:
And the code is:
SELECT GROUP_CONCAT(text) as final_text
FROM main WHERE HSA = 'YES'
...
- 75