Question 21158 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql Substr index and multiple group by conditions

I’m trying to get the totals of a table grouped by names that are similar. Here is my test mysql:

    CREATE TABLE IF NOT EXISTS aa (
     clicks INT NOT NULL, 
     locn varchar (30) NOT NULL
    ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
          
    insert into aa values(1, 'page?1'),   
                         (1, 'page?2'),
                         (1, 'page?3'),
                         (1, 'item(12)'),                     
                         (1, 'item(22)'),                     
                         (1, 'item(32)');

If I run this command

    SELECT count(clicks), substring_index(locn, '(',1) as a FROM aa group by a;

the result is

    3 item
    1 page?1
    1 page?2
    1 page?3

If I run

    SELECT count(clicks), substring_index(locn, '?',1) as b FROM aa group by b ;

the result is

    1 item(12)
    1 item(22)
    1 item(32)
    3 page

So each of those work but I can’t figure out how to get them to work together. Would someone explain how to do this, please?

Tags:

2

Answers


  1. 0 
    SELECT count(clicks), substring_index(substring_index(locn, '(', 1), '?', 1) as a 
FROM aa group by a;

    Result given your data:

    +---------------+------+
| count(clicks) | a    |
+---------------+------+
|             3 | page |
|             3 | item |
+---------------+------+
    Login or Signup to reply.
  2. 0 
    SELECT REGEXP_SUBSTR(locn, '\w+'), COUNT(*)
FROM aa
GROUP BY 1
    REGEXP_SUBSTR(locn, ‘w+’) COUNT(*)
    page 3
    item 3

    fiddle

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

Back To Top
Search