We have a table of items, these items MAY or MAY NOT contain the size as part of the item id e.g. ABC/1, ABC/2, DEF. If the size is part of the item id, it is always written as <item>/<size> where <item> is a variable length and <size> is a single character.
When calculating sales figures, I am able to SUM() on quantity for each itemid, however, I would prefer to group all items of the same range as one (e.g. ABC/1, ABC/2 ABC/3 as a range called ABC, and therefore SUM() all ABC figures into one).
Is this possible as part of the query, will I have to use a stored procedure?
Current query: SELECT ITEMID, SUM(QTY) AS SOLD FROM CUSTINVOICETRANS GROUP BY ITEMID