CODEWITHSUNDEEP
mysqlgroup-by
Keith Leyson
Keith Leyson

Reputation: 65

How to group by with substring in MySQL

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

Upvotes: 5

Views: 13708

Answers (5)

pujitha
pujitha

Reputation: 1

The substr query can be written in mysql. Consider this link as reference.

http://www.w3resource.com/mysql/string-functions/mysql-substr-function.php

The query should be like

SELECT file FROM filestatistics WHERE userid = 99 AND file!=substr("ex",0,2) GROUP BY file;

Upvotes: -1

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

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

Upvotes: 8

etsa
etsa

Reputation: 5060

I think this is more general (eg. case file = 'exp1ex')

    SELECT CASE WHEN substr(file,1,2)='ex' THEN substr(file,3,LENGTH(file)) ELSE file END AS file 
           , COUNT(*) AS RC
    FROM filestatistics 
    WHERE userid = 99 
    GROUP BY CASE WHEN substr(file,1,2)='ex' THEN substr(file,3,LENGTH(file)) ELSE file END;

Upvotes: 0

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

try this:

select replace(file,'ex','') as file,count(userid),userid from filestatistics GROUP BY  userid, replace(file,'ex','')

Upvotes: 0

prava
prava

Reputation: 3986

I haven't tested it, but hope this one will help you :-

SELECT substring_index(file, 'ex', -1) as file_count, count(*)
FROM filestatistics
GROUP BY file_count;

Upvotes: 3

Related Questions