cPanel Question posted in
The official documentation can be seen here.

Mysql: grouping digit regardless of the digit's position – CPanel

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.

Tags:

3

Answers


  1. 0 
    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
    Login or Signup to reply.
  2. 0 
    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)
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search