Is it possible to group by multiple columns (OR statement) in MySQL?

Question

I'd like to group by 2 columns but in "or mode"... I think I'll explain myself better with an example:

ID  |  Col 1  |  Col 2  |   Col 3  | Rest of columns ...
-------------------------------------------------------
1        A         Q         green
2        B         R         blue
3        B         S         red
4        C         T         purple
5        D         U         orange
6        E         R         black
7        F         U         brown
8        F         V         pink
9        G         W         white

So ... Grouping by column 1 we have rows 2+3 and 7+8 merged in results Grouping by column 2 we have rows 2+6 and 7+5 merged in results

But I'd like to group between them, so the SQL results (in this case) in 2 rows in output:

rows 2, 3 and 6 in a group
rows 5, 7, and 8 in other group
among other unique rows

Visual explanation of the 1) group:

Visual explanation of the 2) group:

Is this possible in a unique SQL ? Or is it better to handle this data "merging" in programming code ?

So the result selecting a concat of the column 3, could be:

blue,red,black
brown,pink,orange

(and then the rest that are "unique" of the two groupings...)
green
purple
white

So it's like a "by group" but taking in account 2 columns :-P It's difficult to explain, but I hope visual examples talk by their own.

BTW: I'm in MySQL 8 ... no matter which one, if I have to upgrade or change, I can do it ;-)

Also, you haven't told us how we should know which column (`Col1` or `Col2`) should be used for a any given row. — Dai, Aug 12 '21 at 12:28
"Grouping by column 1 we have rows 2+3 and 7+8" - no, doing a `GROUP BY Col1` will give you `( 1, 2+3, 4, 5, 6, 7+8, 9 )`, not `( 2+3 )` - or do you want an implicit `HAVING COUNT(*) >= 2`? — Dai, Aug 12 '21 at 12:28
What do the values in your proposed grouping of rows have in common? I can't tell from your question how you know to group 5,7, and 8 together. — O. Jones, Aug 12 '21 at 12:30
show a complete [mre] and the endresult that you want. the exact version helps also — nbk, Aug 12 '21 at 12:30
This would need arrays, which mysql doesn't support,, so do it in a stored procedure or in another language — nbk, Aug 12 '21 at 13:08

score 2 · Answer 1 · answered Aug 12 '21 at 14:47

Based on your description and end result

You can use this.

With additional subqueries you can make it you can gather more information as it is needed

CREATE TABLE tab1 (
  `ID` VARCHAR(35),
  `Col 1` VARCHAR(35),
  `Col 2` VARCHAR(35),
  `Col 3` VARCHAR(35)
);

INSERT INTO tab1
  (`ID`, `Col 1`, `Col 2`, `Col 3`)
VALUES
  ('1'        ,'A'         ,'Q' ,        'green'),
  ('2'        ,'B'         ,'R'   ,     'blue'),
  ('3'   ,     'B',         'S',         'red'),
  ('4'  ,      'C',         'T' ,        'purple'),
  ('5'        ,'D'        , 'U'     ,    'orange'),
  ('6'      ,  'E'         ,'R'         ,'black'),
  ('7'        ,'F'      ,  'U' ,        'brown'),
  ('8'       ,'F'       ,  'V' ,        'pink'),
  ('9',        'G ',        'W'      ,   'white');

SELECT
`Col 1`, col2
, CONCAT(col3,',', (SELECT GROUP_CONCAT(`Col 3`) FROM tab1 WHERE FIND_IN_SET(`Col 2`,col2) AND NOT FIND_IN_SET(`Col 3`,col3)))

FROM (SELECT `Col 1`, GROUP_CONCAT(`Col 2`) as col2, GROUP_CONCAT(`Col 3`) col3 
FROM tab1 
GROUP BY `Col 1` 
HAVING COUNT(*) > 1) t1
UNION
SELECT `Col 1`, `Col 2`, `Col 3`
FROM tab1
GROUP BY `Col 2`
HAVING COUNT(*) = 1

Col 1 | col2 | CONCAT(col3,',', (SELECT GROUP_CONCAT(`Col 3`) FROM tab1 WHERE FIND_IN_SET(`Col 2`,col2) AND NOT FIND_IN_SET(`Col 3`,col3)))
:---- | :--- | :---------------------------------------------------------------------------------------------------------------------------
B     | R,S  | blue,red,black                                                                                                              
F     | U,V  | brown,pink,orange                                                                                                           
A     | Q    | green                                                                                                                       
B     | S    | red                                                                                                                         
C     | T    | purple                                                                                                                      
F     | V    | pink                                                                                                                        
G     | W    | white

db<>fiddle here

forpas · Accepted Answer · 2021-08-12T16:18:19.243

2

For this sample data you can use a self join:

SELECT t1.col1, 
       GROUP_CONCAT(t2.col3) col3
FROM tablename t1 INNER JOIN tablename t2
ON t2.id = t1.id OR t2.col2 = t1.col2
GROUP BY t1.col1
HAVING COUNT(DISTINCT t1.id) > 1 OR COUNT(t2.id) = 1

See the demo.
Results:

col1	col3
A	green
B	blue,red,black
C	purple
F	orange,brown,pink
G	white

I guess it is a typo in your expected results.

edited Aug 12 '21 at 16:18

answered Aug 12 '21 at 15:22

forpas

160,666
10
38
76

Is it possible to group by multiple columns (OR statement) in MySQL?

2 Answers2