Groupby question

microsoft.public.sqlserver.programming

8 Mar 2007 10:15 PM

Blasting Cap

I have a table with the following data in a varchar field (the "10
01-2007" below) and a number (units).

10 01-2007 150
11 01-2007 300
12 01-2007 575
14 01-2007 25
21 01-2007 50000
22 01-2007 25000
31 01-2007 8700
51 01-2007 330

I need to combine the ones with 10, 11 & 12 into one line, number 14 on
one line, 21 & 22 into one line, and 31 & 51 on a single line each
respectively.

The groupby is currently on the first 2 characters of the varchar field,
which gives me 3 separate entries for 10 11 & 12, 2 separate entries on
21 & 22, etc.

Group1 01-2007 150
Group1 01-2007 300
Group1 01-2007 575
Group2 01-2007 25
Group3 01-2007 50000
Group3 01-2007 25000
Group4 01-2007 8700
Group5 01-2007 330

I have to evaluate the first two characters of that field to determine
which group it's supposed to be in.

How do you do a groupby to give you:

Group1 01-2007 1025
Group2 01-2007 25
Group3 01-2007 75000
Group4 01-2007 8700
Group5 01-2007 330

Thanks,

BC