GROUP BY on modified field (T-SQL) ?

[dravine]

Currently I have:

SELECT TOP (100) PERCENT MAX(REPLACE(REPLACE(ID_I, 'L', ''), 'H', '')) AS ID_I, SUM(AMOUNT) AS AMOUNT
FROM I_TABLE
WHERE (ID_I LIKE '00099001%')
GROUP BY REPLACE(REPLACE(ID_I, 'L', ''), 'H', ''), AMOUNT
ORDER BY ID_I

1. I want to GROUP BY/merge/combine the ID_I field which features two nested REPLACE() functions. How do I go about doing this ? Currently it’s probably querying the original field value – I want it to query the modified value (after REPLACE(..) has been evaluated) (inspiration for this: http://stackoverflow.com/questions/3313783/replace-and-group-by-in-sql)

2. AMOUNT (a total) of the two entries with identical ID_I .. So two entries with the same ID_I (after REPLACE(..)) – but this doesn’t work (probably because of the first problem ?)

[Author]

Posts