CODEWITHSUNDEEP
mysqlgroup-by
Tyler Nichol
Tyler Nichol

Reputation: 655

Group by partial string

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
----------+-----------+--------------

Upvotes: 2

Views: 6935

Answers (3)

jmilloy
jmilloy

Reputation: 8365

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

Upvotes: 4

Taryn
Taryn

Reputation: 247680

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

Upvotes: 7

Pentium10
Pentium10

Reputation: 207863

CAST the first column to a number, add an index to the newly created column and run a group by sum on that column.

update table set `left`=CAST(col1 AS SIGNED );

select `left`,sum(*),`col3` from table;

Upvotes: 0

Related Questions