getting distinct values of a column 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.

if i put in a count(distinct DealerCD), teradata throws out a error saying "distinct is not permitted in a over() clause"
Any help would be highly appreciated.

thanks
bcd

 

[joedsilva]

how about something like this

SELECT T.STOREID, T.DEALERCD, T.DEALERNAME, T.DEALERPERMIT
,SUM(CASE WHEN T.PREVDEALERCD IS NULL OR T.PREVDEALERCD <> T.DEALERCD THEN 1 ELSE 0 END) OVER(PARTITION BY STOREID)
FROM
(
SELECT S.STOREID, S.DEALERCD, S.DEALERNAME, S.DEALERPERMIT
,MAX(DEALERCD) OVER(PARTITION BY STOREID ORDER BY DEALERCD ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) PREVDEALERCD
FROM MYDATATBL S
) T
;

 

[bcdixit]

joedsilva,
this is exactly what i wanted!!!
awesome!..thanks a ton.

-bd