Avoid Aggregate Distinct in T-SQl (Sql Server 2008)Posted: November 25, 2009
Here is a discussion about the hamful effect of Aggregate Distinct syntax in SQL query (SQL Server 2008).
The proposed solution is to use two steps to get the aggregates.
1. Get a partial aggregation derived table; 2. calculate the final aggrgation from the derived table.
;with PartialSums as (
SELECTCOUNT(*) AS countstarpartialcount FROM myTest GROUP BY pvalue
SELECTSUM(countstarpartialcount) AS countstar, SUM(1) AS countproductkeys FROM partialsums
–" … Note that in sum(1), the 1 is actually a constant number value, not a column reference."