Counting Records - SQL Server 2000

[mpatdweb]

I need to count rooms sold. The problem is that a room can be sold more than once for any given trip.

Example:

Inv. Trip Room
1 A50805 201
2 A50805 202
3 B60805 201
4 B60805 201


The query should return records 1, 2, 3.

Any help greatly appreciated!

[MAK]

use tempdb
go
create table Rooms(Inv int, Trip varchar(10), Room int)
go
insert into Rooms select 1, 'A50805', 201
insert into Rooms select 2, 'A50805', 202
insert into Rooms select 3, 'B60805', 201
insert into Rooms select 4, 'B60805' ,201
insert into Rooms select 5, 'B60805' ,201
insert into Rooms select 6, 'A50805', 202
go
select distinct A.TRIP, A.ROOm,
(select min(B.inv) from Rooms B where a.trip=b.trip and a.room =b.room) as INV
from Rooms A
go

select Count(inv) from (
select distinct A.TRIP, A.ROOm,
(select min(B.inv) from Rooms B where a.trip=b.trip and a.room =b.room) as INV
from Rooms A) as mytable

go