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:

FROM Sales.SalesOrderHeader)
SELECT CountOfCust,
SOH.CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader AS SOH

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 the editor of the online journal Simple Talk. I love talking about SQL Server to anyone who will listen, just ask my seven year old granddaughter. I love to write and teach. I am so humbled and honored to say I am a Data Platform MVP, a volunteer with LaunchCode and co-leader of PASS Women in Technology Virtual Group.
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: Logo

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

Facebook photo

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

Connecting to %s