Using Distinct with Window Aggregates

This past Saturday I presented my T-SQL Window Function Performance session at the Atlanta SQL Saturday. There were some interesting questions from the audience that didn’t actually pertain to performance. I didn’t have all the answers since there were some questions about things I hadn’t tried.

One question was about problems with using DISTINCT and windowing functions. I’m assuming that the question pertained to window aggregates, because when I experimented later with a window aggregate example, I received this error “Use of DISTINCT is not allowed with the OVER clause.”

Here is the query I tried:

SELECT COUNT(DISTINCT CustomerID) OVER(), CustomerID,     SalesOrderID
FROM sales.SalesOrderHeader
ORDER BY CustomerID;

To get around this, you could just use a CTE to get your distinct count and not use a windowing function at all. Here is a possible solution:

;WITH Cust AS(SELECT COUNT(DISTINCT CustomerID) AS CountOfCust
FROM Sales.SalesOrderHeader)
SELECT CountOfCust,
SOH.CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader AS SOH
CROSS JOIN Cust;

Since window aggregate performance is not great, you may want to take this alternate approach anyway.  I keep learning new things about windowing functions, and I’ll also keep passing along what I learn.

 

 

About Kathi Kellenberger

I am a SQL Server professional working for Linchpin People. I love talking about SQL Server to anyone who will listen, just ask my two year old granddaughter. I love to write and teach. I am so humbled and honored to say that I made SQL Server MVP in 2013.
This entry was posted in T-SQL Window Functions. Bookmark the permalink.

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