Menu
I have written this and successfully executed in Oracle
COUNT (DISTINCT APEC.COURSE_CODE) OVER (
PARTITION BY s.REGISTRATION_NUMBER
,APEC.APE_ID
,COV.ACADEMIC_SESSION
) APE_COURSES_PER_ACADEMIC_YEAR
I'm trying to achieve the same result in SQL Server (our source database uses Oracle but our warehouse uses SQL Server).
I know the distinct isn't supported with window functions in SQL Server 2008 - can anyone suggest an alternative?
397
Count Distinct is not supported by window partitioning, we need to find a different way to achieve the same result.
Msg 10759, Level 15, State 1, Line 1 Use of DISTINCT is not allowed with the OVER clause. There are, however, a few relatively simple workarounds that are suprisingly efficient.
The correct syntax for using COUNT(DISTINCT) is: SELECT COUNT(DISTINCT Column1) FROM Table; The distinct count will be based off the column in parenthesis. The result set should only be one row, an integer/number of the column you're counting distinct values of.
Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows. SELECT COUNT(DISTINCT yourColumnName) AS anyVariableName FROM yourTableName; To understand the above syntax, let us create a table. Display all records from the table using select statement.
Here's what I recently came across. I got it from this post. So far it works really well for me.
DENSE_RANK() OVER (PARTITION BY PartitionByFields ORDER BY OrderByFields ASC) +
DENSE_RANK() OVER (PARTITION BY PartitionByFields ORDER BY OrderByFields DESC) - 1 AS DistinctCount
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
