SELECT DISTINCT + COUNT with some lastIndexOf?

What I'm trying to do here is to extract the last part of a string. The strings always ends up the same way : thisismy-string. So i se开发者_如何学运维arch for '-' in reverse to extract 'string' (which can be anything). ie: 'hello-world' to get 'world'

SELECT DISTINCT REVERSE(SUBSTR(REVERSE(field_name), 1, INSTR(REVERSE(field_name), '-')-1)) AS grp FROM table GROUP BY grp ORDER BY id

Which works fine. Now how can i count the number of times, SQL found the same 'end-of-string' while doing its DISTINCT function ? Is it possible to combine that in the same query ? I'm using this query for a PHP project...

SELECT
    COUNT(*) AS `occurrences`,
    SUBSTRING_INDEX(`field_name`, '-', -1) AS `last_part`
FROM table
GROUP BY `last_part`

SUBSTRING_INDEX([field or string], [delimiter], -1) will get you the last part (due to the -1), and COUNT() respects grouping

Edit: Your title says something about "lastIndexOf", by which I assume you mean the highest id for each of the groupings. That you can do by adding MAX(`id`) to the fields you're selecting

SELECT
    COUNT(*) AS `occurrences`,
    SUBSTRING_INDEX(`field_name`, '-', -1) AS `last_part`,
    MAX(`id`) AS `last_index_of`
FROM table
GROUP BY `last_part`

Example

id  | field_name
------------------------
1   | foo-bar
2   | woo-bar
3   | woo-foo-baz
4   | foo-foo
5   | foo-boo
6   | foo-bar
7   | foo-baz
8   | kung-foo
9   | xyz-xyz
10  | 123-xyz

Result:

occurrences | last_part | last_index_of
----------------------------------------
3           | bar       | 6
2           | baz       | 7
1           | boo       | 5
2           | foo       | 8
2           | xyz       | 10