Avoid Aggregate Distinct in T-SQl (Sql Server 2008)

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 (


COUNT(*) AS countstarpartialcount FROM myTest  GROUP BY pvalue



SUM(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."


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s