Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

finding distinct values in a partition query

Status
Not open for further replies.

bcdixit

Technical User
Nov 11, 2005
64
0
0
US
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
 
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]
 
Thanks JarlH, the query works on other databases but unfortunately this doesnot work on Teradata. sorry should have mentioned that earlier.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top