MYSQL: Using GROUP BY with string literals

Question

I have the following table with these columns:

shortName, fullName, ChangelistCount

Is there a way to group them by a string literal within their fullName? The fullname represents file directories, so I would like to display results for certain parent folders instead of the individual files.

I tried something along the lines of:

GROUP BY fullName like "%/testFolder/%" AND fullName like "%/testFolder2/%"

However it only really groups by the first match....

Thanks!

Answer 1

You'd have to use a subquery to derive the column values you'd like to ultimately group on:

    FROM (SELECT SUBSTR(fullname, ?)AS derived_column
            FROM YOUR_TABLE ) x
GROUP BY x.derived_column

Answer 2

Perhaps you want something like:

GROUP BY IF(fullName LIKE '%/testfolder/%', 1, IF(fullName LIKE '%/testfolder2/%', 2, 3))

The key idea to understand is that an expression like fullName LIKE foo AND fullName LIKE bar is that the entire expression will necessarily evaluate to either TRUE or FALSE, so you can only get two total groups out of that.

Using an IF expression to return one of several different values will let you get more groups.

Keep in mind that this will not be particularly fast. If you have a very large dataset, you should explore other ways of storing the data that will not require LIKE comparisons to do the grouping.