Pat, Suppress duplicate rows only suppresses rows after aggregation which means that any counts will be incorrectly multiplied by any duplicate rows in the dataset.

It would also allow for multiple counts on different fields to be done in the same query output as currently adding multiple counts() will only return the same value based on the query logic.

Counting different fields at the same time would be useful for generating profile reports such as number of Event Registrations, number of Education Involvements etc.

It will also help to avoid logic errors where the user has an OR statement that has two true values for example if I create a query for Constituent\Gender = Male Or Constituent\Age = 35. Any Male constituents aged 35 will be counted twice if the regular Count() is used, but count(distinct ID) would only count that constituent once.

Thank you for your quick and detailed responses. Setting this back to Open for voting.

yes exactly what Jefferson said, it's when you want to see if someone has maybe only one payment method or multiple, you could do a count distinct on payment method

The DISTINCT count requested in this idea is different than the Suppress duplicate rows. That functionality is SELECT DISTINCT <stuff> FROM <TABLE>. I believe what's being requested is SELECT COUNT(DISTINCT <column>) that returns the number of distinct values in that column. It's a bit of a bad example, but Lookup ID, COUNT(Amount) would return the number of gifts given where Lookup ID, COUNT(DISTINCT Amount) would return the number of different amounts a constituent had given.

There is a check box on the ad-hoc query definition editor’s “Set save options” tab to “Suppress duplicate rows.” This adds a DISTINCT modifier to the SELECT statement