CODEWITHSUNDEEP
mysql
Edward Tanguay
Edward Tanguay

Reputation: 193452

How to group by last item in a field value in MySQL?

I have a field named "ColorCode" in a MySQL table that has values such as these: 

28373 GYY green
23423 HH red
23423 HH green  
2343 QWE red    
SKDJF blue  
green

What would be the SQL statement to show how many occurances of each color, something like this PHP-influenced pseudo code:

SELECT COUNT(*) FROM products GROUP BY END(EXPLODE(ColorCode));

Upvotes: 1

Views: 82

Answers (2)

user1726343
user1726343

Reputation:

How about this:

GROUP BY SUBSTRING_INDEX(ColorCode,' ',-1);

This doesn't work for your last example though.

Evidently it does work for your last example too! :D

Here is a demonstration: http://sqlfiddle.com/#!2/d41d8/4088

Upvotes: 1

Marc B
Marc B

Reputation: 360872

fugly, but something like this

group by RIGHT(ColorCode, LOCATE(' ', REVERSE(ColorCode))

If you're running this query frequently, definitely consider splitting that 'last' value into its own field so you can at least index it. performance is going to be abysmal on large tables.

Upvotes: 4

Related Questions