How to group by with substring in MySQL

Question

I have a table called filestatistics:

id    file    userid    datetime
 1    p1          99    2017-04-15 09:05:10
 2    exp1        99    2017-04-15 09:25:17
 3    p2          99    2017-04-15 09:45:46
 4    exp2        99    2017-04-15 09:55:07

and I want to group and get the total entries per user them according to their filename but excluding the 'ex' string.

My query is:

SELECT file FROM filestatistics WHERE userid = 99 GROUP BY file;

My query above results in 4 entries which is wrong.

What would be the correct query to group the files per user?

Desired output is

2 files for userid = 99 
//since p1 and exp1 are grouped as 1
//since p2 and exp2 are grouped as 1

Answer 1

You can use the following solution using REPLACE to get the files:

SELECT userid, REPLACE(file, 'ex', '') AS file, COUNT(id) AS cnt_files
FROM filestatistics 
GROUP BY REPLACE(file, 'ex', ''), userid

To get the COUNT of files of each user you can use the following:

SELECT COUNT(DISTINCT REPLACE(file, 'ex', '')) AS cnt_files
FROM filestatistics 
GROUP BY userid

demo on dbfiddle.uk