Group by partial string

Question

Here is my table:

----------+-----------+--------------
45678-sm-w|   18      | Mens clothing
----------+-----------+--------------
45678-sm-b|   5       | Mens clothing
----------+-----------+--------------
2189-L-Wh |   4       | Juniors Clothing
----------+-----------+--------------
2189-L-Bl |   3       | Juniors Clothing
----------+---------- +--------------

This is what the desired outcome is:

----------+-----------+--------------
45678     |   23      | Mens clothing
----------+-----------+--------------
2189      |   7       | Juniors Clothing
----------+-----------+--------------

Answer 1

If you are selecting the data, then you can use something like this:

select SUBSTRING_INDEX(col1, '-', 1) col1, 
  sum(col2) Total, 
  col3
from table1
group by SUBSTRING_INDEX(col1, '-', 1), SUBSTRING_INDEX(col3, ' ', 1)
order by col1 desc

See SQL Fiddle With Demo

Answer 2

Can you alter the table? It would make the most sense to alter the table by splitting col1 into three columns (e.g. id, size, and color), so that you can just group by the new id column:

-----+----+----+-----------+--------------
45678| sm | w  |   18      | Mens clothing
-----+----+----+-----------+--------------
45678| sm | b  |   5       | Mens clothing
-----+----+----+-----------+--------------
2189 |  L | Wh |   4       | Juniors Clothing
-----+----+----+-----------+--------------
2189 |  L | Bl |   3       | Juniors Clothing
-----+----+----+---------- +--------------

You can accomplish this by using ALTER TABLE to add the three columns you need and change the primary key, using SUBSTRING_INDEX and UPDATE to extract and store the split values, and another ALTER TABLE to cleanup. Something like this:

ALTER TABLE t
DROP PRIMARY KEY;

ALTER TABLE T
ADD id INT UNSIGNED NOT NULL,
ADD size VARCHAR(255),
ADD color VARCHAR(255),
ADD PRIMARY KEY (col1);

UPDATE TABLE t
SET id = CONVERT(SUBSTRING_INDEX(col1, '-', 1), UNSIGNED INTEGER),
SET size = SUBSTRING_INDEX(col1, '-', 2),
SET color = SUBSTRING_INDEX(col1, '-', 3);

ALTER TABLE t
DROP COLUMN col1;

---

If you can't alter the table, you can extract the id number you need using SUBSTRING_INDEX:

SELECT SUBSTRING_INDEX(col1, '-', 1), col2, col3 FROM table;

gives:

-----+-----------+--------------
45678|   18      | Mens clothing
-----+-----------+--------------
45678|   5       | Mens clothing
-----+-----------+--------------
2189 |   4       | Juniors Clothing
-----+-----------+--------------
2189 |   3       | Juniors Clothing
-----+---------- +--------------

For the final query, I would use one SELECT to extract the id you need, and an outer SELECT for the grouping:

SELECT id, SUM(col2) AS total, col3
FROM 
    (SELECT SUBSTRING_INDEX(col1, '-', 1) AS id, col2, col3
    FROM table) AS t
GROUP BY id