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?

>Solution :

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 |
+---------------+------+

Leave a Reply