T-SQL Query Help Needed

[realJSOP]

I have a view that contains a list transactions, each of which are associated with a single routing number. Multiple transactions can exist for each routing number. Each transaction can be one of two types (a and b). I need to get the routing numbers, the count of transactions for each routing number, and then the count of those that are of type b, and the result set can only include transactions of type b (along with the requisite counts, of course). Can I get some guidance?

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

[Corporal Agarn]

One way is to have a nested query

select a.[transaction], b.tran_count
from transactiontable A
inner join (select [transaction], count(*) as tran_count from transactiontable group by [transaction]) as b
on a.[trasaction] = b.[transaction];

This is an off-the-cuff answer so it needs some looking into, but should start you down the right path.

[Tim Carmichael]

select RoutingNUmber, count(TransactionNumber) as TransCount from TransTable where TransType = 'B' group by RoutingNUmber Does that help? Tim

[realJSOP]

I'll give it a try tomorrow. We came up with a three-select query, but it's evident that we know not what we do. :)

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

[realJSOP]

Nah, but that's okay - we have a solution (to use the term loosely). :)

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001