Count and Group By

[supercooper]

I have data like so:

LSEID MTRS
------------------------------------------
AR-001 5-6N-27W-5
AR-001 5-6N-27W-5
AR-001 5-6N-27W-5
AR-002 5-6N-27W-5
AR-003 5-6N-27W-5
AR-003 5-6N-27W-5


and I need the count of LSEIDs in a unique MTRS, as in above
in 5-6N-27W-5, there are 3 unique LSEIDs

I have tried all combos of count, distinct, group by I can think of
until my eyes are bulging. I think this is really easy and I'm just
stumped. I'm thinking I may need to left join the count results to the
mtrs group results like so (??):

select mtrs from tbl group by mtrs as r1
left join (somehow get the count of each lseid for unique mtrs's) as
r2
on r1.mtrs on r2.mtrs

Any ideas??

 

[Marshall Barton]

I have data like so:

LSEID MTRS
------------------------------------------
AR-001 5-6N-27W-5
AR-001 5-6N-27W-5
AR-001 5-6N-27W-5
AR-002 5-6N-27W-5
AR-003 5-6N-27W-5
AR-003 5-6N-27W-5


and I need the count of LSEIDs in a unique MTRS, as in above
in 5-6N-27W-5, there are 3 unique LSEIDs

I have tried all combos of count, distinct, group by I can think of
until my eyes are bulging. I think this is really easy and I'm just
stumped. I'm thinking I may need to left join the count results to the
mtrs group results like so (??):

select mtrs from tbl group by mtrs as r1
left join (somehow get the count of each lseid for unique mtrs's) as
r2
on r1.mtrs on r2.mtrs

SELECT MTRS, Count(*) As mtrscount
FROM [SELECT DISTINCT MTRS, LSEID
FROM table]. As T
GROUP BY MTRS