I haves a mysql query which gets the total number of rows which are either 0 or above 300 and it works fine except I need the query to group some of the rows together.

Here's the table:

```
+------------------------------+---------------------+------------+
| color                        | check_date          | color_code |
+------------------------------+---------------------+------------+
| red                          | 2017-06-20 13:52:18 | 408        |
| green                        | 2017-06-17 19:10:25 | 524        |
| green                        | 2017-06-19 12:17:31 | 314        |
| light-red                    | 2017-06-19 10:51:05 | 227        |
| dark-green                   | 2017-06-25 10:47:50 | 82         |
| dark-blue                    | 2017-06-21 09:23:43 | 0          |
| light-blue                   | 2017-06-20 09:24:50 | 900        |
| yellow                       | 2017-06-19 11:01:46 | 833        |
| medium-yellow                | 2017-06-07 16:07:49 | 0          |
| medium-red                   | 2017-06-16 09:46:38 | 19         |   
| dark-green                   | 2017-06-21 08:47:50 | 822        |
| dark-blue                    | 2017-06-01 15:23:43 | 12         |
| light-blue                   | 2017-06-22 11:24:50 | 900        |
| yellow                       | 2017-06-21 09:23:43 | 8          |
| medium-yellow                | 2017-06-17 14:07:49 | 11         |
| light-blue                   | 2017-06-16 09:46:38 | 19         |
+------------------------------+---------------------+------------+

```

Here's the query:

```
SELECT color, 
max(check_date) AS check_date, 
count(*) AS total, 
sum( color_code < 1 OR color_code >= 300) AS cnt 
FROM check_colors 
GROUP BY color 
ORDER BY cnt DESC LIMIT 10;

```

This will return a table like this:

```
+------------------------------+---------------------+-------+------+
| color                        | check_date          | total | cnt  |
+------------------------------+---------------------+-------+------+
| light-blue                   | 2017-06-22 11:24:50 |   3   |   2  |
| green                        | 2017-06-19 12:17:31 |   2   |   2  |
| dark-green                   | 2017-06-25 10:47:50 |   2   |   1  |
| medium-yellow                | 2017-06-17 14:07:49 |   2   |   1  |
| dark-blue                    | 2017-06-21 08:47:50 |   2   |   1  |
| yellow                       | 2017-06-21 09:23:43 |   2   |   1  |
| red                          | 2017-06-20 13:52:18 |   1   |   1  |
| light-red                    | 2017-06-19 10:51:05 |   1   |   0  |
| medium-red                   | 2017-06-16 09:46:38 |   1   |   0  |
+------------------------------+---------------------+-------+------+

```

What I need is a mysql query that combines the light, dark and medium prefixes with the unprefixed colors. For example instead of having green and dark-green as seperate rows they would be combined and the color column would be displayed as just green. The results would be total: 4, cnt: 3.

I need the reults table from the above query to look like this: 

```
+------------------------------+---------------------+-------+------+
| color                        | check_date          | total | cnt  |
+------------------------------+---------------------+-------+------+
| blue                         | 2017-06-22 11:24:50 |   5   |   3  |
| green                        | 2017-06-25 10:47:50 |   4   |   3  |
| yellow                       | 2017-06-21 09:23:43 |   4   |   2  |
| red                          | 2017-06-20 13:52:18 |   3   |   1  |
+------------------------------+---------------------+-------+------+

```

Check date would the most recent out of the light, dark, medium or unprefixed colors.

If I do this in PHP before or after the mysql it would skew the results, so has to be in mysql.

Maybe I could use regex in mysql somehow?

Thanks :)