INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

getting distinct values of a column in a partition query

getting distinct values of a column in a partition query

getting distinct values of a column in a partition query

(OP)
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

RE: getting distinct values of a column in a partition query

how about something like this

CODE

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
;

RE: getting distinct values of a column in a partition query

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

-bd

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close