finding distinct values in a partition query

[bcdixit]

I have a data set that looks something like this in a table

storeID DealerCD DealerName DealerPermit
------- -------- ---------- ----------
1111 980038 John PAC
1111 980041 Bill NAC
1111 980041 Bill TTR
1131 987789 Greg PAC
1131 983434 Rudy PAC
1131 988232 Amy NAC
1131 988234 Chris NAC

What I want is this.

storeID DealerCD DealerName DealerPermit Count
------- -------- ---------- ---------- -----
1111 980038 John PAC 2
1111 980041 Bill NAC 2
1111 980041 Bill TTR 2
1131 987789 Greg PAC 4
1131 983434 Rudy PAC 4
1131 988232 Amy NAC 4
1131 988234 Chris NAC 4


What I want to do is partition by storeID and for each store I want to count the distinct dealers in that store. The query that I have is something like this

select storeID, DealerCD,DealerName,DealerPermit,
count(DealerCD) over (partition by storeid) from Mytable
group by storeID, DealerCD, DealerName, DealerPermit.

what I am getting is for storeID 1111 I am getting the count as 3 and for storeID 1131 as 4. I want to get the count=2 for storeID = 1111 since there are only two distinct dealers working in that store.

Any help would be highly appreciated.

thanks
bcd

 

[JarlH]

Maybe something like:
[tt]
select storeID, DealerCD,DealerName,DealerPermit,
(SELECT COUNT(DISTINCT DealerName) FROM Mytable
where Mytable.storeID = M.storeID)
from Mytable as M
[/tt]

 

[bcdixit]

Thanks JarlH, the query works on other databases but unfortunately this doesnot work on Teradata. sorry should have mentioned that earlier.