SUM() by GROUPS

下記データを１つのテーブルに格納してたい。

<データ>

Skiff Medical: $10

Skiff Medical: $20

Skiff Medical: $30

Straub Medical: $10

Straub Medical: $15

Straub Medical: $25

<求めたい結果>

Need to produce:

Skiff Medical: $60

Straub Medical : $50

CREATE TABLE #OneColumnOnly

(

TheOneColumn) VARCHAR(100)

)

INSERT INTO #OneColumnOnly(TheOneColumn))

SELECT 'Skiff Medical: $10'

INSERT INTO #OneColumnOnly(TheOneColumn)

SELECT 'Skiff Medical: $10'

INSERT INTO #OneColumnOnly(TheOneColumn)

SELECT 'Skiff Medical: $20'

INSERT INTO #OneColumnOnly(TheOneColumn)

SELECT 'Skiff Medical: $30'

INSERT INTO #OneColumnOnly(TheOneColumn)

SELECT 'Straub Medical: $10'

INSERT INTO #OneColumnOnly(TheOneColumn)

SELECT 'Straub Medical: $15'

INSERT INTO #OneColumnOnly(TheOneColumn)

SELECT 'Straub Medical: $15'

INSERT INTO #OneColumnOnly(TheOneColumn)

SELECT 'Straub Medical: $25'

＜回答例＞

SELECT

LEFT(TheOneColumn,CHARINDEX(':',TheOneColumn)) ,

SUM(CAST(RIGHT(TheOneColumn,LEN(TheOneColumn) - CHARINDEX('$',TheOneColumn)) AS INT))

FROM #OneColumnOnly

Group by

LEFT(TheOneColumn,CHARINDEX(':',TheOneColumn))

Skiff Medical: 70

Straub Medical: 65

(2 row(s) affected)