SQL Server 2005 - Invalid Query: CUBE and ROLLUP cannot compute distinct aggregates.
In Microsoft SQL Server 2005, when GROUP BY is executed with ROLLUP or CUBE options you can not compute or calculate distinct aggregates like COUNT(DISTINCT column_name), etc.
select
ApplianceGroupId, ApplianceTypeId, count(distinct brandid)
from efsb
inner join appliances on appliances.applianceid = efsb.applianceid
group by ApplianceGroupId, ApplianceTypeId
with rollup
Msg 8617, Level 16, State 1, Line 2
Invalid Query: CUBE and ROLLUP cannot compute distinct aggregates.
If distinct aggregates are executed with in aggregate t-sql functions like Count(), the error message "Invalid Query: CUBE and ROLLUP cannot compute distinct aggregates." is returned.
Unlike MS SQL Server 2005, SQL Server 2008 (Katmai) can manage computing distinct aggregates successfully.
When I got the above invalid query error in the SQL Server Management Studio, I wondered whether will it work or fail in the November CTP (CTP5) release of SQL Server 2008.
I ran the below query, which is similar to above sql queries which ran successfully on the new SQL Server database engine.
select
u.id userid,
w.id wallpaperid,
count(distinct vote_value)
from votes v
inner join wallpapers w on v.wallpaperid = w.id
inner join users u on u.id = w.userid
group by u.id, w.id
with rollup
select
u.id userid,
w.id wallpaperid,
vote_value
from votes v
inner join wallpapers w on v.wallpaperid = w.id
inner join users u on u.id = w.userid
where u.id = 3
If you check the results of the two query, you can see how successfully aggregate functions with distinct keyword are executed by SQL Server. If you compare the above results for userid 3 with the below result set, you see that for wallpaperid 4 there are two distinct votes, and for wallpaperid 5 there is only one vote, at last for wallpaperid 6 there are two different values of votes.
For the ROLLUP part for userid 3 where wallpaperid is NULL, you can see 3 distinct values are suggested in the first query result set.
The second sql result set shows that these 3 different vote values are 10, 9 and 7.
So, goodbye to "Invalid Query: CUBE and ROLLUP cannot compute distinct aggregates." with next coming version of Microsoft SQL Server, MS SQL Server 2008 (aka Katmai).