Merge Rows in SQL Server 2008

Question

I have a table like the one below:

Customer   |Type     |Count
Joe        |Silver-S |1
Joe        |Silver   |7
Joe        |Gold     |3
Joe        |Gold-S   |2

I need to merge this so that it looks like the following:

Customer   |Type     |Count
Joe        |Silver   |8
Joe        |Gold     |5

Help!

Answer 1

select Customer, [Type], SUM([Count]) from (
    select Customer, replace([Type], '-S', '') [Type], [COUNT] from Customer
)
t
group by customer, [Type]

Answer 2

Here is a solution utilising a partition approach:

SELECT DISTINCT Customer, REPLACE([Type], '-S', '') AS [Type], 
SUM([Count]) OVER (PARTITION BY (SELECT REPLACE([Type], '-S', ''))) AS [Count]
FROM Customer