SQL Complex Grouping using group sub-string.

Data:
ID name val1 val2
1 aaa01 10 100
2 bbb01 20 200
3 aaa02 30 300
4 bbb02 40 400


Result:
aaa 40 400
bbb 60 600



Solution:
CREATE TABLE #t (ID INT, name VARCHAR(10), val1 INT, val2 INT)

INSERT #t  (ID , name , val1 , val2 )
SELECT 1, 'aaa01', 10, 100
UNION ALL
SELECT 2, 'bbb01', 20, 200
UNION ALL
SELECT 3, 'aaa02', 30, 300
UNION ALL
SELECT 4, 'bbb02', 40, 400

SELECT SUBSTRING( name, 1, 3),  
SUM (val1),
SUM (val2)
FROM #t GROUP BY SUBSTRING(name, 1, 3)

DROP TABLE #t