CODEWITHSUNDEEP
mysqlsql
user14461410
user14461410

Reputation: 31

Grouping strings together based on common first words

I have a list of different items, some of these have a size and colour description at the end. E.g. Men's Football Jacket - L / Black.

I'd like to be able to group all Men's Football Jacket products regardless of size or colour.

╔═══════════════════════════════════╤══════╗
║ Item                              │ Sold ║
╠═══════════════════════════════════╪══════╣
║ Men's Football Jacket - L / Blue  │ 5    ║
╟───────────────────────────────────┼──────╢
║ Men's Football Jacket - M / Black │ 3    ║
╟───────────────────────────────────┼──────╢
║ Women's Socks - S                 │ 8    ║
╟───────────────────────────────────┼──────╢
║ Women's Socks - L                 │ 10   ║
╟───────────────────────────────────┼──────╢
║ Gloves                            │ 12   ║
╚═══════════════════════════════════╧══════╝

Output:
╔═══════════════════════╤══════╗
║ Item                  │ Sold ║
╠═══════════════════════╪══════╣
║ Men's Football Jacket │ 8    ║
╟───────────────────────┼──────╢
║ Women's Socks         │ 18   ║
╟───────────────────────┼──────╢
║ Gloves                │ 12   ║
╚═══════════════════════╧══════╝

As you can see from the example, some items only have size in the string, other have neither size nor colour specified.

I'm using MySQL if that makes a difference.

Upvotes: 0

Views: 47

Answers (2)

forpas
forpas

Reputation: 164139

If you want to group by the values of Item up to the 1st -, use SUBSTRING_INDEX() to extract that part of the string:

SELECT SUBSTRING_INDEX(Item, '-', 1) Item, 
       SUM(Sold) Sold
FROM tablename
GROUP BY SUBSTRING_INDEX(Item, '-', 1)

See the demo.
Results:

> Item                  | Sold
> :-------------------- | ---:
> Gloves                |   12
> Men's Football Jacket |    8
> Women's Socks         |   18

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

If the values are always separated by space-hyphen-space, then just use substring_index():

select substring_index(item, ' - ', 1) as item,
       sum(sold)
from t
group by substring_index(item, ' - ', 1) ;

Upvotes: 1

Related Questions