I have a table with following set of data
zipcode code siteid contactid segcd priority
02043 H8 1022722357 1321362004 L 1
02043 H8 1022722357 7301144221 M 2
02043 H8 1022722357 7251214784 N 3
02061 H8 1022733344 1424607099 A 1
02061 H8 1022733344 7207855516 B 2
02061 H8 1022733344 1487264648 C 3
I want to roll up the contact ids to site ids based on the priority assigned to the segcd.
i.e Contactid's having same siteid's should be rolled to that segment having least priority.
Example.
Site id 1022722357 has 3 different contact ids with 3 different segcd's . So the three contacts
1321362004,7301144221,7251214784
should be rolled to the segcd L which is having least priority .
In other words my output should like this
Zipcode Code SegCd count(Siteid) count(contactid)
02043 H8 L 1 3
02061 H8 A 1 3
How can I exactly achieve this.
Any help would be greatly helpful
Thanks
SRK