mysql query to combine multiple values as one

Question

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 :)

Why would it "skew the results" to do it after the query? Your query looks fine; just loop through the result that don't contain a "-" first to build a list of the colors, then loop through the ones that do contain a "-" and update the associated "base color." — Scott C Wilson
– Scott C Wilson, Commented Jun 21, 2017 at 11:22
The alternative would be that you do string manipulation in your GROUP BY clause ... — C3roe
– C3roe, Commented Jun 21, 2017 at 11:24

Passionate Coder · Accepted Answer · 2017-06-21 11:32:05Z

2

You can use SUBSTRING_INDEX for doing this.

Have an example

Table

value   color
10  light-red
4   red
7   red
2   light-pink
4   dark-pink
6   medium-pink
6   pink


SELECT SUBSTRING_INDEX( color,  '-', -1) AS color, SUM( value )  as Total
FROM count
GROUP BY SUBSTRING_INDEX( color,  '-', -1)

Output

color   Total
pink    18
red     21

http://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.php

answered Jun 21, 2017 at 11:32

Passionate Coder

7,3042 gold badges24 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

khurram · Accepted Answer · 2017-06-21 11:24:48Z

1

you can use

SUBSTRING_INDEX('light-green','-',-1)

function to achieve this , use the function in select and group by clause.

answered Jun 21, 2017 at 11:24

khurram

313 bronze badges

Comments

Sanjay Barnwal · Accepted Answer · 2017-06-21 11:58:15Z

1

Following modification in your query should work :

SELECT SUBSTRING_INDEX( color, '-', -1) AS single_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 SUBSTRING_INDEX( color, '-', -1)

ORDER BY cnt DESC LIMIT 10;

answered Jun 21, 2017 at 11:58

Sanjay Barnwal

2111 silver badge4 bronze badges

Collectives™ on Stack Overflow

mysql query to combine multiple values as one

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related