MYSQL: Using GROUP BY with string literals

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开发者_JAVA技巧 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!

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.

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

Either use when/then conditions or Have another temporary table containing all the matches you wish to find and group. Sample from my database.

Here I wanted to group all users based on their cities which was inside address field.

SELECT ut.* , c.city, ua.*
FROM  `user_tracking` AS ut
LEFT JOIN cities AS c ON ut.place_name LIKE CONCAT(  "%", c.city,  "%" )
LEFT JOIN users_auth AS ua ON ua.id = ut.user_id