grouping question 1

[jby1]

Hi

I have a table that can contain numerous records with the same transaction id, each of which can be set to one of a number of statuses. I would like a query that will only select me transaction ids where they are all set to a specified status.

For example, for this table (run this in Query Analyzer)

declare @t table(trans_id int, status int)  
insert @t values(1, 1)
insert @t values(1, 2)
insert @t values(1, 2)

insert @t values(2, 2)
insert @t values(2, 2)
insert @t values(2, 2)

insert @t values(3, 1)
insert @t values(3, 1)
insert @t values(3, 1)

select trans_id, status from @t

I would like a query which returns me trans_id 2, and nothing else, if I specify that I want all transactions where the status is all set to 2.

Can anybody help me?

 

[jby1]

I have worked out a solution (although it may not be the optimum one)

declare @t table(trans_id int, status int)  
insert @t values(1, 1)
insert @t values(1, 2)
insert @t values(1, 2)

insert @t values(2, 2)
insert @t values(2, 2)
insert @t values(2, 2)

insert @t values(3, 1)
insert @t values(3, 1)
insert @t values(3, 1)


select a.trans_id
from
(
	select trans_id
	from @t
	where status = 2
	group by trans_id
) a
left join
(
	select trans_id
	from @t
	where status <> 2
	group by trans_id
) b
on a.trans_id = b.trans_id
where b.trans_id is null

 

[bborissov]

declare @t table(trans_id int, status int)  
insert @t values(1, 1)
insert @t values(1, 2)
insert @t values(1, 2)

insert @t values(2, 2)
insert @t values(2, 2)
insert @t values(2, 2)

insert @t values(3, 1)
insert @t values(3, 1)
insert @t values(3, 1)

SELECT Tbl1.*
       FROM @t Tbl1
INNER JOIN
      (SELECT trans_id,(SUM(Status)*1.0/COUNT(*)) AS Status
              FROM @t
              GROUP BY trans_id) Tbl2
ON Tbl1.trans_id = Tbl2.trans_id AND Tbl2.Status = 2

(not tested very well)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jby1]

Thanks Borislav, I am liking your solution very much