using Regular Expression in Group by Clause

group-by, mysql, regex, sql

Solution

SELECT CONCAT(SUBSTRING_INDEX(Item_Code, '-', 1), '-', 
              SUBSTRING_INDEX(Item_Code, '-', -1)) AS Category_Color,
       SUM(Quantity) AS Quantity
FROM items
GROUP BY 1;

Example: SQLFiddle

Problem

I have a table 'items' ``` Item_Code |Rate |Quantity -------------------------------------- JOGGER-POWER-BLACK |699 |5 JOGGER-POWER-WHITE |599 |8 JOGGER-PLAZA-WHITE |1199 |2 SLEEPER-PLAZA-BLACK |699 |6 SLEEPER-POWER-BLACK |499 |5 ``` I want to Group by Part1-%-Part3 of Item_Code like following which is just idea ``` select (part1 and part3 of Item_Code) as 'Category-Color',sum(Quantity) as Quantity group by (part1 and part3 of Item_Code) ``` By Parts I mean looking Item_Code in terms of Hyphen Desired Output ``` Category-Color|Quantity ------------------- JOGGER-BLACK |5 JOGGER-WHITE |10 SLEEPER-BLACK |11 ``` "regular expressions in group by clause","group by with regular expression". Google/Bing giving nothing relevant I am unable to understand whether database developers are not required to provide this feature or how they do it. I am required such grouping by the Store owner. e.g Category (Part1) wise Sum of quantities.

Original source