MySQL Grouping Rows in Feed Database Table – Output Grouped Rows

MySQL

I'm trying to write an SQL query to work on a raw feed that's been imported to a MySQL database table. The table's rows loosely resemble this example.

| sku        | group_name | description       | size | type   |
|------------|------------|-------------------|------|--------|
| EX-1000-XS | NULL       | Long text         | XS   | single |
| EX-1000-S  | NULL       | Long text         | S    | single |
| EX-1000-M  | NULL       | Long text         | M    | single |
| EX-1000-L  | NULL       | Long text         | L    | single |
| EX-1001-M  | EX-1001    | Another long text | M    | single |
| EX-1001-L  | EX-1001    | Another long text | L    | single |
| EX-1001    | NULL       |                   |      | group  |

I'm trying to get the ones without a group_name, but with the determined group name set, along with a group row. Ignoring stuff that already was in a grouping. So the output I want from the query is like this.

| sku        | group_name | description       | size | type   |
|------------|------------|-------------------|------|--------|
| EX-1000-XS | EX-1000    | Long text         | XS   | single |
| EX-1000-S  | EX-1000    | Long text         | S    | single |
| EX-1000-M  | EX-1000    | Long text         | M    | single |
| EX-1000-L  | EX-1000    | Long text         | L    | single |
| EX-1000    | NULL       |                   |      | group  |

I've figured out some substring function on the sku column, to get a "key" like value, 

SELECT
  SUBSTRING_INDEX(
    sku,
    '-',
    LENGTH(sku) - LENGTH(REPLACE(sku, '-', ''))
  )
FROM
  products
WHERE
  group_name IS NULL
  AND type = "single";

No idea how to use that as a grouping key yet,

I set up a fiddle with the example, https://www.db-fiddle.com/f/ua6iwXGHcPDVKbCkqHvs2E/0

Best Answer

db-fiddle has strange effect on columns are not in order as per sql statement but works fine on my local Mysql 8.0

select sku,
       case  WHEN group_name IS NULL
             AND  type='single'
             THEN  TRIM(TRAILING '-' FROM REPLACE(sku,size,''))
              ELSE group_name                  
        END 
        AS group_name,
        description,
        size,
        type
from products

This gives your desired output with cte expressions i.e excluding existing rows plus grouping key(same output as bottom table in your question).Tested on your dbfiddle

WITH cte1
AS
  (
   SELECT sku,
          TRIM(TRAILING '-' FROM REPLACE(sku,size,''))      
          group_name,
          description,
          size,
          type
   FROM   products
   WHERE  group_name IS NULL
   AND    type = 'single'
  )

SELECT * FROM cte1

UNION

SELECT   DISTINCT(group_name) sku,
         NULL group_name,
         '' description,
         '' size,
         'group' type
FROM     cte1
Related Question