>>There is a CountDistinct function in Reporting Services that "Returns the number of non-null unique instances of an item within a set". I would like to do that in a SQL query, but can't seem to find my brain this morning. There are just three items in the query, ItemA, ItemB, and the Number (or count) of unique values for ItemB with respect to itself. In other words, if ItemB had values P1, P3, and P7, and if P1 occurred 347 times, and P3 occurred 231, etc., these values would appear in ColumnC.
>>
>>All help is appreciated.
>
>
>SELECT ItemA, COUNT(DISTINCT ItemB ) FROM mytable GROUP BY ItemA
>
Well, here is the exact code, and it includes ItemB (program), which seems to through things off.
SELECT PersonID, Program, COUNT(DISTINCT Program) AS ProgramCount
FROM dbo.tmmLicesnses2Persons
WHERE (DateActivated IS NOT NULL) AND (DateDeactivated IS NULL)
GROUP BY PersonID, Program
Thank You
Rollin Burr
Politicians and diapers have one thing in common. They should both be changed regularly, and for the same reason.