Mysql: grouping digit regardless of the digit's position

0
votes

This sounds a little bit confusing and I don't know how to put it in words, but I'm having a hard time finding a solution of this problem.

I want to 'group by' the rows and count them in the table using the 'digit' column that has the same digits regardless of the digit's position.

example:

this is the table

Id Name Digit
1 name1 123
2 name2 213
3 name3 132
4 name4 122
5 name5 456

the answer would be: with count()

Name Digit Count
name1 123 3
name4 122 1
name5 456 1

additional details:

  • the digit column is numeric
  • length fixed(3)
  • the 'name' column display must be any from the group (could be name1, name2, or name3)

mysql version is 82 from cpanel.

3
mysqlsql
Why you delete previous question? mysql version is 82 from cpanel. Such version not exists. Execute SELECT VERSION(); and provide complete output. - Akina
I suggest storing a sorted version of each Digit value at the time when you write out each record. Do this from your application layer, where it is much easier to handle. Swinging this from MySQL is going to be fairly ugly (and non performant). - Tim Biegeleisen
Does zero 0 is present in the values? Does it may be (formally) leading? - Akina
1+2+3 = 2+1+3=3+2+1 etc = 6 - P.Salmon
Is it always 3 digits? - Strawberry

3 Answers

1
votes

You can think the single digits of the Digit column as coordinates in a 3D space. If the coordinates are swapped the resulting vector will have the same lenght. You can use this property to create an efficient query:

SELECT Name, Digit, COUNT(*) as C
FROM t
GROUP BY(
    POW(CAST(SUBSTRING(digit, 1, 1) AS UNSIGNED),2) +   -- first digit
    POW(CAST(SUBSTRING(digit, 2, 1) AS UNSIGNED),2) +   -- second digit
    POW(CAST(SUBSTRING(digit, 3, 1) AS UNSIGNED),2)     -- third digit
);

I don't calculate the square root because we are not interested in the exact length. I named the column c instead of count because COUNT is reserved word in SQL.

0
votes
WITH 
parsed AS ( SELECT id, name, Digit DIV 100 val FROM test
            UNION ALL
            SELECT id, name, Digit MOD 100 DIV 10 FROM test
            UNION ALL
            SELECT id, name, Digit MOD 10 FROM test ),
sorted AS ( SELECT id, name, GROUP_CONCAT(val ORDER BY val SEPARATOR '') Digit
            FROM parsed
            GROUP BY id, name )
SELECT MIN(name) Name, Digit, COUNT(*) `Count`
FROM sorted
GROUP BY Digit
0
votes
drop table if exists t;
create table t(id int,name varchar(20),digit int);
insert into t values
(1, 'name1',    123),
(2, 'name2',    213),
(3, 'name3',    132),
(4, 'name4',    122),
(5, 'name5',    456),
(6, 'name5',    77),
(7, 'name5',    1),
(8, 'name5',    1);

    with cte as
    (
    select distinct substring(digit,1,1) + substring(digit,2,1) + substring(digit,3,1) digit from t
    )
    select min(id) id,min(name) name,min(t.digit) digit,count(*) cnt
    from   cte
    join   t on   substring(t.digit,1,1) + substring(t.digit,2,1) + substring(t.digit,3,1) = cte.digit
    group  by cte.digit;

+------+-------+-------+-----+
| id   | name  | digit | cnt |
+------+-------+-------+-----+
|    7 | name5 |     1 |   2 |
|    4 | name4 |   122 |   1 |
|    1 | name1 |   123 |   3 |
|    6 | name5 |    77 |   1 |
|    5 | name5 |   456 |   1 |
+------+-------+-------+-----+
5 rows in set, 15 warnings (0.002 sec)