Geek asks and answers

Using regex in a group by condition

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 from Item_Code, as after that, it's just an idea

select (part1 and part3 of Item_Code) as 'Category-Color',sum(Quantity)
as Quantity group by (part1 and part3 of Item_Code)

In parts, I mean searching for Item_Code in terms of Hyphen

Desired Result

Category-Color|Quantity
-------------------
JOGGER-BLACK  |5
JOGGER-WHITE  |10
SLEEPER-BLACK |11

"regular expressions in a group by clause", "group by with regular expression". Google / Bing gives nothing.

I can’t understand if the database developers need to provide this function or how they do it. I need such a grouping by the store owner. for example, a category (part 1) multiplied by the number of quantities.

+5
source share
1 answer
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

+4
source

More articles:


All Articles