I have some data like this:
MyKey    MyKind
1                180
2                181
3                162
4                170
5                183
6                169
7                169
8                171

I would like to get a listing like this:
MyGroup                             Count
Group1(162, 169)                    3
Group2(170, 171)                    2
Group3(180, 181, 183)            3

Which values that belongs to a group could well be hardcoded in the SQL, or
could be put in as params along with the "GroupName"
Any Ideas?

Regards
Petter Topp
Dbisam 4

Petter


This approach will need hard coding but I've just tested it and it works


select if( _surname='Smyth','Smith',_surname) as surname ,count(_surname) from contacts
where _surname like 'Sm%'
group by surname

Roy Lambert

select
 case
   when MyKind in (162, 169) then 'Group1'
   when MyKind in (170, 171) then 'Group2'
   when MyKind in (180, 181, 183) then 'Group3'
   else 'GroupX'
 end
as MyGroup,
count (MyKey) as GroupCount
from YourTable
group by MyGroup


"Petter Topp" <petter.topp@atcdata.no> wrote in message
news:0F9BAD87-D50C-4831-8FE3-DC3249DEB706@news.elevatesoft.com...
>I have some data like this:
> MyKey    MyKind
> 1                180
> 2                181
> 3                162
> 4                170
> 5                183
> 6                169
> 7                169
> 8                171
>
> I would like to get a listing like this:
> MyGroup                             Count
> Group1(162, 169)                    3
> Group2(170, 171)                    2
> Group3(180, 181, 183)            3
>
> Which values that belongs to a group could well be hardcoded in the SQL,
> or could be put in as params along with the "GroupName"
> Any Ideas?
>
> Regards
> Petter Topp
> Dbisam 4