MySQL Select multiple columns group by sorted columns values

Question

I have this table columns structure:

id - n1 - n2 - n3

And here it is with some dummy data:

id - n1 - n2 - n3
1 - 3 - 2 - 1
2 - 6 - 5 - 7
3 - 2 - 3 - 1
4 - 1 - 6 - 5
5 - 5 - 6 - 7
6 - 3 - 5 - 6

And the idea is to Select and count each unique distinct group of n1, n2 and n3 in sequence.

So, for example, we could get this result:

total - n1s - n2s - n3s
2 - 1 - 2 - 3
2 - 5 - 6 - 7
1 - 1 - 5 - 6
1 - 3 - 5 - 6

Can you help me set the state to achieve that??

I am trying to attempt that without multiple selects and PHP array sorting...

Thanks.

Answer 1

Consider the following - a normalised dataset...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL
,n INT NOT NULL
,val INT NOT NULL
,PRIMARY KEY(id,n)
);

INSERT INTO my_table VALUES
(1, 1, 3),
(1, 2, 2),
(1, 3, 1),
(2, 1, 6),
(2, 2, 5),
(2, 3, 7),
(3, 1, 2),
(3, 2, 3),
(3, 3, 1),
(4, 1, 1),
(4, 2, 6),
(4, 3, 5),
(5, 1, 5),
(5, 2, 6),
(5, 3, 7),
(6, 1, 3),
(6, 2, 5),
(6, 3, 6);

Here's a quick (to write) and dirty solution. Faster / more elegant solutions are available...

SELECT vals
     , COUNT(*) total
  FROM 
     ( SELECT id
            , GROUP_CONCAT(val ORDER BY val) vals 
         FROM my_table 
        GROUP 
           BY id
     ) x 
 GROUP 
    BY vals;
+-------+-------+
| vals  | total |
+-------+-------+
| 1,2,3 |     2 |
| 1,5,6 |     1 |
| 3,5,6 |     1 |
| 5,6,7 |     2 |
+-------+-------+

Answer 2

We just need expressions to "sort" the values in columns n1, n2 and n3. If we have that, then we can do a simple GROUP BY and COUNT.

SELECT COUNT(1) AS total
     , IF(t.n1<=t.n2,IF(t.n1<=t.n3,t.n1,t.n3),IF(t.n2<=t.n3,t.n2,t.n3)) AS n1s
     , IF(t.n1<=t.n2,IF(t.n2<=t.n3,t.n2,IF(t.n1<=t.n3,t.n3,t.n1)),IF(t.n1<=t.n3,t.n1,IF(t.n2<=t.n3,t.n3,t.n2 ))) AS n2s
     , IF(t.n1<=t.n2,IF(t.n2<=t.n3,t.n3,t.n2),IF(t.n1<=t.n3,t.n3,t.n1)) AS n3s
  FROM this_table_column_structure t
 GROUP BY n1s,n2s,n3s
 ORDER BY total DESC, n1s, n2s, n3s

will return

total   n1s   n2s   n3s
-----  ----  ----  ----
    2     1     2     3
    2     5     6     7
    1     1     5     6
    1     3     5     6

Answer 3

As a first approach (if time permits), you should really consider normalizing your table, as suggested in @Strawberry's answer

However, a second approach allowing any number of columns (although inefficient due to String operations and Bubble Sorting) is possible, utilizing User Defined Functions.

We basically need to create a function, which can sort the values inside a comma separated string. I found a working function, which can do the sorting. Reproducing code from here:

-- sort comma separated substrings with unoptimized bubble sort
DROP FUNCTION IF EXISTS sortString;
DELIMITER |
CREATE FUNCTION sortString(inString TEXT) RETURNS TEXT
BEGIN
  DECLARE delim CHAR(1) DEFAULT ','; -- delimiter 
  DECLARE strings INT DEFAULT 0;     -- number of substrings
  DECLARE forward INT DEFAULT 1;     -- index for traverse forward thru substrings
  DECLARE backward INT;   -- index for traverse backward thru substrings, position in calc. substrings
  DECLARE remain TEXT;               -- work area for calc. no of substrings
-- swap areas TEXT for string compare, INT for numeric compare
  DECLARE swap1 TEXT;                 -- left substring to swap
  DECLARE swap2 TEXT;                 -- right substring to swap
  SET remain = inString;
  SET backward = LOCATE(delim, remain);
  WHILE backward != 0 DO
    SET strings = strings + 1;
    SET backward = LOCATE(delim, remain);
    SET remain = SUBSTRING(remain, backward+1);
  END WHILE;
  IF strings < 2 THEN RETURN inString; END IF;
  REPEAT
    SET backward = strings;
    REPEAT
      SET swap1 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward-1),delim,-1);
      SET swap2 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward),delim,-1);
      IF  swap1 > swap2 THEN
        SET inString = TRIM(BOTH delim FROM CONCAT_WS(delim
        ,SUBSTRING_INDEX(inString,delim,backward-2)
        ,swap2,swap1
        ,SUBSTRING_INDEX(inString,delim,(backward-strings))));
      END IF;
      SET backward = backward - 1;
    UNTIL backward < 2 END REPEAT;
    SET forward = forward +1;
  UNTIL forward + 1 > strings
  END REPEAT;
RETURN inString;
END |
DELIMITER ;

You will need to run this code on your MySQL server, so that this function is available within a query, just like native built-in MySQL functions. Now, the querying part becomes simple. All you need to do is Concat_ws() all the number columns using comma. And, then apply sortString() function on the concatenated string. Eventually, use the "ordered" string in Group By clause, to get the desired result.

Try:

SELECT sortString(CONCAT_WS(',', n1, n2, n3)) AS n_sequence -- add more columns here
       COUNT(id) AS total 
FROM your_table 
GROUP BY n_sequence 
ORDER BY total DESC 

Now I suggest that you can use your application code to change comma separated n_sequence back to tabular column display.