Aggregation not working for Count Distinct

[nbailey]

Hoping someone can help me with this problem...

I have a simple cross tab report in report studio ( see enclosure for examples)

when I create and run this report as a single query I get the desired results.  My measure is student ID, defined as a character.  I am doing a Count( distinct pidm_key) on this ID field to get a count for the number of students without duplication. 

My Problem starts when I use this query as sub query and feed the results to an outer query and output the total to the same report.
Something is happening in the aggregation that I don't understand where I my count is increased from 779 to 845.   I have tried every combination of aggregation on the inner and outer query that I can think of and nothing works.   What am I missing ???

Note :  the exact same report without the count distinct ( simple count of all ID'S ) works fine. 

See enclosure for details.

Thanks in advance for any helpp you can give. 

[blom0344]

This may be due to the database performing a rewrite on the SQL.  Again, running traces will probably show what happens on the database. In the past with DB2 I noticed that the use of inline distinct clauses were rewritten when executed on the database.  In SQL server this does not seem to happen

[nbailey]

Thank you for the reply...
I just found the problem and it far more simple than i would have imagined.   If you look at my data items list in the attachment you will notice dbname,  Academic year, campus Code, Registration Status code and FirstYr. 
Registration stutus code is used in the filter but not in the report. 
But when I use this query as a sub query Cognos is use Registration Status in the group by clause.  That is not what I want.  When I remove it from the data Items list in all the sub queries and the outer report query... my counts come out correct.   Always learning something new about COGNOS.   :)